How to show missing rows with default values





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}







0















I have a table Category which has a few missing records for a few months. The table looks like this



Category    Month       Amount
Opt January 12.00
Opt February 0.00
Opt March -1042000.00
Opt April 0.00
Opt May 0.00
Opt June 0.00
Opt July -782663.00
Opt November 0.00
Opt December 0.00


This table has missing rows for a few months. I would like to query and show this data as well.



So I created a month table



MonthNameLong   MonthNameShort
January Jan
February Feb
March Mar
April Apr
May May
June Jun
July Jul
August Aug
September Sep
October Oct
November Nov
December Dec


So that I can do a right join on this and get the missing details as well. The query I wrote is



select s.Category, m.MonthNameLong, coalesce(s.Amount, 0.0) as Amount
from Category s
RIGHT JOIN Month m
ON s.Month = m.MonthNameLong


And the result I get is



Category    MonthNameLong   Amount
Opt January 12.00
Opt February 0.00
Opt March -1042000.00
Opt April 0.00
Opt May 0.00
Opt June 0.00
Opt July -782663.00
NULL August 0.00
NULL September 0.00
NULL October 0.00
Opt November 0.00
Opt December 0.00


But instead of NULL category is there a way I can get Opt as the category.



I have attached the fiddle here https://www.db-fiddle.com/f/7sycoGZ4sYxNzdYeTvJauC/0



This is very similar to SQL join query to show rows with non-existent rows in one table question but that has all the data in the table so there isn't missing rows.










share|improve this question














bumped to the homepage by Community 9 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.






















    0















    I have a table Category which has a few missing records for a few months. The table looks like this



    Category    Month       Amount
    Opt January 12.00
    Opt February 0.00
    Opt March -1042000.00
    Opt April 0.00
    Opt May 0.00
    Opt June 0.00
    Opt July -782663.00
    Opt November 0.00
    Opt December 0.00


    This table has missing rows for a few months. I would like to query and show this data as well.



    So I created a month table



    MonthNameLong   MonthNameShort
    January Jan
    February Feb
    March Mar
    April Apr
    May May
    June Jun
    July Jul
    August Aug
    September Sep
    October Oct
    November Nov
    December Dec


    So that I can do a right join on this and get the missing details as well. The query I wrote is



    select s.Category, m.MonthNameLong, coalesce(s.Amount, 0.0) as Amount
    from Category s
    RIGHT JOIN Month m
    ON s.Month = m.MonthNameLong


    And the result I get is



    Category    MonthNameLong   Amount
    Opt January 12.00
    Opt February 0.00
    Opt March -1042000.00
    Opt April 0.00
    Opt May 0.00
    Opt June 0.00
    Opt July -782663.00
    NULL August 0.00
    NULL September 0.00
    NULL October 0.00
    Opt November 0.00
    Opt December 0.00


    But instead of NULL category is there a way I can get Opt as the category.



    I have attached the fiddle here https://www.db-fiddle.com/f/7sycoGZ4sYxNzdYeTvJauC/0



    This is very similar to SQL join query to show rows with non-existent rows in one table question but that has all the data in the table so there isn't missing rows.










    share|improve this question














    bumped to the homepage by Community 9 mins ago


    This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.


















      0












      0








      0








      I have a table Category which has a few missing records for a few months. The table looks like this



      Category    Month       Amount
      Opt January 12.00
      Opt February 0.00
      Opt March -1042000.00
      Opt April 0.00
      Opt May 0.00
      Opt June 0.00
      Opt July -782663.00
      Opt November 0.00
      Opt December 0.00


      This table has missing rows for a few months. I would like to query and show this data as well.



      So I created a month table



      MonthNameLong   MonthNameShort
      January Jan
      February Feb
      March Mar
      April Apr
      May May
      June Jun
      July Jul
      August Aug
      September Sep
      October Oct
      November Nov
      December Dec


      So that I can do a right join on this and get the missing details as well. The query I wrote is



      select s.Category, m.MonthNameLong, coalesce(s.Amount, 0.0) as Amount
      from Category s
      RIGHT JOIN Month m
      ON s.Month = m.MonthNameLong


      And the result I get is



      Category    MonthNameLong   Amount
      Opt January 12.00
      Opt February 0.00
      Opt March -1042000.00
      Opt April 0.00
      Opt May 0.00
      Opt June 0.00
      Opt July -782663.00
      NULL August 0.00
      NULL September 0.00
      NULL October 0.00
      Opt November 0.00
      Opt December 0.00


      But instead of NULL category is there a way I can get Opt as the category.



      I have attached the fiddle here https://www.db-fiddle.com/f/7sycoGZ4sYxNzdYeTvJauC/0



      This is very similar to SQL join query to show rows with non-existent rows in one table question but that has all the data in the table so there isn't missing rows.










      share|improve this question














      I have a table Category which has a few missing records for a few months. The table looks like this



      Category    Month       Amount
      Opt January 12.00
      Opt February 0.00
      Opt March -1042000.00
      Opt April 0.00
      Opt May 0.00
      Opt June 0.00
      Opt July -782663.00
      Opt November 0.00
      Opt December 0.00


      This table has missing rows for a few months. I would like to query and show this data as well.



      So I created a month table



      MonthNameLong   MonthNameShort
      January Jan
      February Feb
      March Mar
      April Apr
      May May
      June Jun
      July Jul
      August Aug
      September Sep
      October Oct
      November Nov
      December Dec


      So that I can do a right join on this and get the missing details as well. The query I wrote is



      select s.Category, m.MonthNameLong, coalesce(s.Amount, 0.0) as Amount
      from Category s
      RIGHT JOIN Month m
      ON s.Month = m.MonthNameLong


      And the result I get is



      Category    MonthNameLong   Amount
      Opt January 12.00
      Opt February 0.00
      Opt March -1042000.00
      Opt April 0.00
      Opt May 0.00
      Opt June 0.00
      Opt July -782663.00
      NULL August 0.00
      NULL September 0.00
      NULL October 0.00
      Opt November 0.00
      Opt December 0.00


      But instead of NULL category is there a way I can get Opt as the category.



      I have attached the fiddle here https://www.db-fiddle.com/f/7sycoGZ4sYxNzdYeTvJauC/0



      This is very similar to SQL join query to show rows with non-existent rows in one table question but that has all the data in the table so there isn't missing rows.







      sql-server join






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 9 '18 at 5:20









      thebenmanthebenman

      1065




      1065





      bumped to the homepage by Community 9 mins ago


      This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







      bumped to the homepage by Community 9 mins ago


      This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
























          1 Answer
          1






          active

          oldest

          votes


















          0














          I have updated your fiddle with:




          • add one more category for to see the query is correct when there is more than 1 category

          • alter amounts making category amount sets different and altering zero values for to see the records inserted additionally.


          Edited fiddle.



          The final query is



          With 
          Categories AS (
          SELECT DISTINCT Category
          FROM Category
          ),
          MonthCategories AS (
          SELECT m.MonthNameLong, cat.Category
          FROM Month m, Categories cat
          )

          select mc.Category, mc.MonthNameLong, coalesce(cat.Amount, 0.0) as Amount
          FROM MonthCategories mc
          LEFT JOIN Category cat ON cat.Month = mc.MonthNameLong
          AND cat.Category = mc.Category;


          Of course you may "pack" the query combining CTEs to one and/or moving CTEs to subquery.



          PS. If there is only one category in the table you may simply replace s.Category with MAX(s.Category) OVER (ORDER BY s.Category) as Category in output list of your initial query.






          share|improve this answer


























            Your Answer








            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "182"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: false,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f222154%2fhow-to-show-missing-rows-with-default-values%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            0














            I have updated your fiddle with:




            • add one more category for to see the query is correct when there is more than 1 category

            • alter amounts making category amount sets different and altering zero values for to see the records inserted additionally.


            Edited fiddle.



            The final query is



            With 
            Categories AS (
            SELECT DISTINCT Category
            FROM Category
            ),
            MonthCategories AS (
            SELECT m.MonthNameLong, cat.Category
            FROM Month m, Categories cat
            )

            select mc.Category, mc.MonthNameLong, coalesce(cat.Amount, 0.0) as Amount
            FROM MonthCategories mc
            LEFT JOIN Category cat ON cat.Month = mc.MonthNameLong
            AND cat.Category = mc.Category;


            Of course you may "pack" the query combining CTEs to one and/or moving CTEs to subquery.



            PS. If there is only one category in the table you may simply replace s.Category with MAX(s.Category) OVER (ORDER BY s.Category) as Category in output list of your initial query.






            share|improve this answer






























              0














              I have updated your fiddle with:




              • add one more category for to see the query is correct when there is more than 1 category

              • alter amounts making category amount sets different and altering zero values for to see the records inserted additionally.


              Edited fiddle.



              The final query is



              With 
              Categories AS (
              SELECT DISTINCT Category
              FROM Category
              ),
              MonthCategories AS (
              SELECT m.MonthNameLong, cat.Category
              FROM Month m, Categories cat
              )

              select mc.Category, mc.MonthNameLong, coalesce(cat.Amount, 0.0) as Amount
              FROM MonthCategories mc
              LEFT JOIN Category cat ON cat.Month = mc.MonthNameLong
              AND cat.Category = mc.Category;


              Of course you may "pack" the query combining CTEs to one and/or moving CTEs to subquery.



              PS. If there is only one category in the table you may simply replace s.Category with MAX(s.Category) OVER (ORDER BY s.Category) as Category in output list of your initial query.






              share|improve this answer




























                0












                0








                0







                I have updated your fiddle with:




                • add one more category for to see the query is correct when there is more than 1 category

                • alter amounts making category amount sets different and altering zero values for to see the records inserted additionally.


                Edited fiddle.



                The final query is



                With 
                Categories AS (
                SELECT DISTINCT Category
                FROM Category
                ),
                MonthCategories AS (
                SELECT m.MonthNameLong, cat.Category
                FROM Month m, Categories cat
                )

                select mc.Category, mc.MonthNameLong, coalesce(cat.Amount, 0.0) as Amount
                FROM MonthCategories mc
                LEFT JOIN Category cat ON cat.Month = mc.MonthNameLong
                AND cat.Category = mc.Category;


                Of course you may "pack" the query combining CTEs to one and/or moving CTEs to subquery.



                PS. If there is only one category in the table you may simply replace s.Category with MAX(s.Category) OVER (ORDER BY s.Category) as Category in output list of your initial query.






                share|improve this answer















                I have updated your fiddle with:




                • add one more category for to see the query is correct when there is more than 1 category

                • alter amounts making category amount sets different and altering zero values for to see the records inserted additionally.


                Edited fiddle.



                The final query is



                With 
                Categories AS (
                SELECT DISTINCT Category
                FROM Category
                ),
                MonthCategories AS (
                SELECT m.MonthNameLong, cat.Category
                FROM Month m, Categories cat
                )

                select mc.Category, mc.MonthNameLong, coalesce(cat.Amount, 0.0) as Amount
                FROM MonthCategories mc
                LEFT JOIN Category cat ON cat.Month = mc.MonthNameLong
                AND cat.Category = mc.Category;


                Of course you may "pack" the query combining CTEs to one and/or moving CTEs to subquery.



                PS. If there is only one category in the table you may simply replace s.Category with MAX(s.Category) OVER (ORDER BY s.Category) as Category in output list of your initial query.







                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited Nov 9 '18 at 6:36

























                answered Nov 9 '18 at 5:39









                AkinaAkina

                4,9961311




                4,9961311






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Database Administrators Stack Exchange!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f222154%2fhow-to-show-missing-rows-with-default-values%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    Liste der Baudenkmale in Friedland (Mecklenburg)

                    Single-Malt-Whisky

                    Czorneboh