Getting “filler” empty values for a GROUP BY

Multi tool use
Multi tool use












0















Assuming I want to get 'weekly' results from a date range, and the date range contains four separate weeks, but my database only has a record for one week, how can I get filler values for the remaining three weeks?



For example, given this date range: 6/2018 - 7/2018



I run this query:



SELECT
DATE_FORMAT(period, '%m %d') || ' - ' || DATE_FORMAT(period, '%m %d') AS period,
SUM(clicks) AS clicks
FROM tablename
WHERE period >= ? AND period <= ?
GROUP BY YEAR(period), WEEK(period)


With these table rows in the database:



|         period          | clicks |
| 07/01/2018T:00:00:00Z | 1000 |


And I get these query results:



|     period      | clicks |
| Jul 1 - Jul 5 | 1000 |


But I want to get these query results to cover default values for the empty weeks so I can use them to populate a d3 chart:



|     period      | clicks |
| Jul 1 - Jul 5 | 1000 |
| Jun 25 - Jun 23 | 0 |
| Jun 18 - Jun 24 | 0 |
| Jun 12 - Jun 18 | 0 |


Any ideas? This sort of "filler" phenomena should be generic enough to work with other intervals, such as daily, monthly, yearly, hourly.










share|improve this question

























  • In most RDBMS we achieve such results using a Calendar table. You should create your own calendar table and JOIN it. Search for that, you will find plenty of examples here or on google. [stackoverflow.com/questions/36789953/… answer) from me is a good example.

    – Thomas G
    Dec 31 '18 at 12:16













  • JOIN the table with SELECT 1 UNION SELECT 2 ... SELECT 5.

    – Salman A
    Dec 31 '18 at 12:18
















0















Assuming I want to get 'weekly' results from a date range, and the date range contains four separate weeks, but my database only has a record for one week, how can I get filler values for the remaining three weeks?



For example, given this date range: 6/2018 - 7/2018



I run this query:



SELECT
DATE_FORMAT(period, '%m %d') || ' - ' || DATE_FORMAT(period, '%m %d') AS period,
SUM(clicks) AS clicks
FROM tablename
WHERE period >= ? AND period <= ?
GROUP BY YEAR(period), WEEK(period)


With these table rows in the database:



|         period          | clicks |
| 07/01/2018T:00:00:00Z | 1000 |


And I get these query results:



|     period      | clicks |
| Jul 1 - Jul 5 | 1000 |


But I want to get these query results to cover default values for the empty weeks so I can use them to populate a d3 chart:



|     period      | clicks |
| Jul 1 - Jul 5 | 1000 |
| Jun 25 - Jun 23 | 0 |
| Jun 18 - Jun 24 | 0 |
| Jun 12 - Jun 18 | 0 |


Any ideas? This sort of "filler" phenomena should be generic enough to work with other intervals, such as daily, monthly, yearly, hourly.










share|improve this question

























  • In most RDBMS we achieve such results using a Calendar table. You should create your own calendar table and JOIN it. Search for that, you will find plenty of examples here or on google. [stackoverflow.com/questions/36789953/… answer) from me is a good example.

    – Thomas G
    Dec 31 '18 at 12:16













  • JOIN the table with SELECT 1 UNION SELECT 2 ... SELECT 5.

    – Salman A
    Dec 31 '18 at 12:18














0












0








0








Assuming I want to get 'weekly' results from a date range, and the date range contains four separate weeks, but my database only has a record for one week, how can I get filler values for the remaining three weeks?



For example, given this date range: 6/2018 - 7/2018



I run this query:



SELECT
DATE_FORMAT(period, '%m %d') || ' - ' || DATE_FORMAT(period, '%m %d') AS period,
SUM(clicks) AS clicks
FROM tablename
WHERE period >= ? AND period <= ?
GROUP BY YEAR(period), WEEK(period)


With these table rows in the database:



|         period          | clicks |
| 07/01/2018T:00:00:00Z | 1000 |


And I get these query results:



|     period      | clicks |
| Jul 1 - Jul 5 | 1000 |


But I want to get these query results to cover default values for the empty weeks so I can use them to populate a d3 chart:



|     period      | clicks |
| Jul 1 - Jul 5 | 1000 |
| Jun 25 - Jun 23 | 0 |
| Jun 18 - Jun 24 | 0 |
| Jun 12 - Jun 18 | 0 |


Any ideas? This sort of "filler" phenomena should be generic enough to work with other intervals, such as daily, monthly, yearly, hourly.










share|improve this question
















Assuming I want to get 'weekly' results from a date range, and the date range contains four separate weeks, but my database only has a record for one week, how can I get filler values for the remaining three weeks?



For example, given this date range: 6/2018 - 7/2018



I run this query:



SELECT
DATE_FORMAT(period, '%m %d') || ' - ' || DATE_FORMAT(period, '%m %d') AS period,
SUM(clicks) AS clicks
FROM tablename
WHERE period >= ? AND period <= ?
GROUP BY YEAR(period), WEEK(period)


With these table rows in the database:



|         period          | clicks |
| 07/01/2018T:00:00:00Z | 1000 |


And I get these query results:



|     period      | clicks |
| Jul 1 - Jul 5 | 1000 |


But I want to get these query results to cover default values for the empty weeks so I can use them to populate a d3 chart:



|     period      | clicks |
| Jul 1 - Jul 5 | 1000 |
| Jun 25 - Jun 23 | 0 |
| Jun 18 - Jun 24 | 0 |
| Jun 12 - Jun 18 | 0 |


Any ideas? This sort of "filler" phenomena should be generic enough to work with other intervals, such as daily, monthly, yearly, hourly.







mysql sql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Dec 31 '18 at 12:26







Lansana

















asked Dec 31 '18 at 12:13









LansanaLansana

4,83422351




4,83422351













  • In most RDBMS we achieve such results using a Calendar table. You should create your own calendar table and JOIN it. Search for that, you will find plenty of examples here or on google. [stackoverflow.com/questions/36789953/… answer) from me is a good example.

    – Thomas G
    Dec 31 '18 at 12:16













  • JOIN the table with SELECT 1 UNION SELECT 2 ... SELECT 5.

    – Salman A
    Dec 31 '18 at 12:18



















  • In most RDBMS we achieve such results using a Calendar table. You should create your own calendar table and JOIN it. Search for that, you will find plenty of examples here or on google. [stackoverflow.com/questions/36789953/… answer) from me is a good example.

    – Thomas G
    Dec 31 '18 at 12:16













  • JOIN the table with SELECT 1 UNION SELECT 2 ... SELECT 5.

    – Salman A
    Dec 31 '18 at 12:18

















In most RDBMS we achieve such results using a Calendar table. You should create your own calendar table and JOIN it. Search for that, you will find plenty of examples here or on google. [stackoverflow.com/questions/36789953/… answer) from me is a good example.

– Thomas G
Dec 31 '18 at 12:16







In most RDBMS we achieve such results using a Calendar table. You should create your own calendar table and JOIN it. Search for that, you will find plenty of examples here or on google. [stackoverflow.com/questions/36789953/… answer) from me is a good example.

– Thomas G
Dec 31 '18 at 12:16















JOIN the table with SELECT 1 UNION SELECT 2 ... SELECT 5.

– Salman A
Dec 31 '18 at 12:18





JOIN the table with SELECT 1 UNION SELECT 2 ... SELECT 5.

– Salman A
Dec 31 '18 at 12:18












2 Answers
2






active

oldest

votes


















0














rewrite your query as



ifnull( SUM(clicks),0) AS clicks





share|improve this answer
























  • do you really think this is answering the question in any way?

    – Thomas G
    Dec 31 '18 at 12:38











  • ya.if value is null.it returns 0.he wants to show 0 anyway if the value is 0 in myself that return date also @ThomasG

    – Ram
    Dec 31 '18 at 12:47











  • @Ram Problem is, that in this case - without any data row for the given week - there will not be a result row to Group on, so "No row at all".

    – dognose
    Dec 31 '18 at 12:53



















0














You can use this query to generate the weeks and then left join with your current query to get what you want.



DECLARE @StartingFromDate DATETIME = '2018-12-01';
DECLARE @EndingAtDate DATETIME = '2018-12-31';

WITH CTE_DateRange (DateRange)
AS (SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, @EndingAtDate) - DATEDIFF(WEEK, @StartingFromDate, @EndingAtDate), 0)
UNION ALL
SELECT DATEADD(WEEK, 1, DateRange)
FROM CTE_DateRange
WHERE DATEADD(WEEK, 1, DateRange) < @EndingAtDate)
SELECT CTE_DateRange.DateRange
FROM CTE_DateRange
WHERE CTE_DateRange.DateRange BETWEEN @StartingFromDate AND @EndingAtDate





share|improve this answer























    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    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: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    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%2fstackoverflow.com%2fquestions%2f53987394%2fgetting-filler-empty-values-for-a-group-by%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    rewrite your query as



    ifnull( SUM(clicks),0) AS clicks





    share|improve this answer
























    • do you really think this is answering the question in any way?

      – Thomas G
      Dec 31 '18 at 12:38











    • ya.if value is null.it returns 0.he wants to show 0 anyway if the value is 0 in myself that return date also @ThomasG

      – Ram
      Dec 31 '18 at 12:47











    • @Ram Problem is, that in this case - without any data row for the given week - there will not be a result row to Group on, so "No row at all".

      – dognose
      Dec 31 '18 at 12:53
















    0














    rewrite your query as



    ifnull( SUM(clicks),0) AS clicks





    share|improve this answer
























    • do you really think this is answering the question in any way?

      – Thomas G
      Dec 31 '18 at 12:38











    • ya.if value is null.it returns 0.he wants to show 0 anyway if the value is 0 in myself that return date also @ThomasG

      – Ram
      Dec 31 '18 at 12:47











    • @Ram Problem is, that in this case - without any data row for the given week - there will not be a result row to Group on, so "No row at all".

      – dognose
      Dec 31 '18 at 12:53














    0












    0








    0







    rewrite your query as



    ifnull( SUM(clicks),0) AS clicks





    share|improve this answer













    rewrite your query as



    ifnull( SUM(clicks),0) AS clicks






    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Dec 31 '18 at 12:27









    RamRam

    397




    397













    • do you really think this is answering the question in any way?

      – Thomas G
      Dec 31 '18 at 12:38











    • ya.if value is null.it returns 0.he wants to show 0 anyway if the value is 0 in myself that return date also @ThomasG

      – Ram
      Dec 31 '18 at 12:47











    • @Ram Problem is, that in this case - without any data row for the given week - there will not be a result row to Group on, so "No row at all".

      – dognose
      Dec 31 '18 at 12:53



















    • do you really think this is answering the question in any way?

      – Thomas G
      Dec 31 '18 at 12:38











    • ya.if value is null.it returns 0.he wants to show 0 anyway if the value is 0 in myself that return date also @ThomasG

      – Ram
      Dec 31 '18 at 12:47











    • @Ram Problem is, that in this case - without any data row for the given week - there will not be a result row to Group on, so "No row at all".

      – dognose
      Dec 31 '18 at 12:53

















    do you really think this is answering the question in any way?

    – Thomas G
    Dec 31 '18 at 12:38





    do you really think this is answering the question in any way?

    – Thomas G
    Dec 31 '18 at 12:38













    ya.if value is null.it returns 0.he wants to show 0 anyway if the value is 0 in myself that return date also @ThomasG

    – Ram
    Dec 31 '18 at 12:47





    ya.if value is null.it returns 0.he wants to show 0 anyway if the value is 0 in myself that return date also @ThomasG

    – Ram
    Dec 31 '18 at 12:47













    @Ram Problem is, that in this case - without any data row for the given week - there will not be a result row to Group on, so "No row at all".

    – dognose
    Dec 31 '18 at 12:53





    @Ram Problem is, that in this case - without any data row for the given week - there will not be a result row to Group on, so "No row at all".

    – dognose
    Dec 31 '18 at 12:53













    0














    You can use this query to generate the weeks and then left join with your current query to get what you want.



    DECLARE @StartingFromDate DATETIME = '2018-12-01';
    DECLARE @EndingAtDate DATETIME = '2018-12-31';

    WITH CTE_DateRange (DateRange)
    AS (SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, @EndingAtDate) - DATEDIFF(WEEK, @StartingFromDate, @EndingAtDate), 0)
    UNION ALL
    SELECT DATEADD(WEEK, 1, DateRange)
    FROM CTE_DateRange
    WHERE DATEADD(WEEK, 1, DateRange) < @EndingAtDate)
    SELECT CTE_DateRange.DateRange
    FROM CTE_DateRange
    WHERE CTE_DateRange.DateRange BETWEEN @StartingFromDate AND @EndingAtDate





    share|improve this answer




























      0














      You can use this query to generate the weeks and then left join with your current query to get what you want.



      DECLARE @StartingFromDate DATETIME = '2018-12-01';
      DECLARE @EndingAtDate DATETIME = '2018-12-31';

      WITH CTE_DateRange (DateRange)
      AS (SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, @EndingAtDate) - DATEDIFF(WEEK, @StartingFromDate, @EndingAtDate), 0)
      UNION ALL
      SELECT DATEADD(WEEK, 1, DateRange)
      FROM CTE_DateRange
      WHERE DATEADD(WEEK, 1, DateRange) < @EndingAtDate)
      SELECT CTE_DateRange.DateRange
      FROM CTE_DateRange
      WHERE CTE_DateRange.DateRange BETWEEN @StartingFromDate AND @EndingAtDate





      share|improve this answer


























        0












        0








        0







        You can use this query to generate the weeks and then left join with your current query to get what you want.



        DECLARE @StartingFromDate DATETIME = '2018-12-01';
        DECLARE @EndingAtDate DATETIME = '2018-12-31';

        WITH CTE_DateRange (DateRange)
        AS (SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, @EndingAtDate) - DATEDIFF(WEEK, @StartingFromDate, @EndingAtDate), 0)
        UNION ALL
        SELECT DATEADD(WEEK, 1, DateRange)
        FROM CTE_DateRange
        WHERE DATEADD(WEEK, 1, DateRange) < @EndingAtDate)
        SELECT CTE_DateRange.DateRange
        FROM CTE_DateRange
        WHERE CTE_DateRange.DateRange BETWEEN @StartingFromDate AND @EndingAtDate





        share|improve this answer













        You can use this query to generate the weeks and then left join with your current query to get what you want.



        DECLARE @StartingFromDate DATETIME = '2018-12-01';
        DECLARE @EndingAtDate DATETIME = '2018-12-31';

        WITH CTE_DateRange (DateRange)
        AS (SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, @EndingAtDate) - DATEDIFF(WEEK, @StartingFromDate, @EndingAtDate), 0)
        UNION ALL
        SELECT DATEADD(WEEK, 1, DateRange)
        FROM CTE_DateRange
        WHERE DATEADD(WEEK, 1, DateRange) < @EndingAtDate)
        SELECT CTE_DateRange.DateRange
        FROM CTE_DateRange
        WHERE CTE_DateRange.DateRange BETWEEN @StartingFromDate AND @EndingAtDate






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Dec 31 '18 at 12:34









        Riaz RazaRiaz Raza

        11810




        11810






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • 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%2fstackoverflow.com%2fquestions%2f53987394%2fgetting-filler-empty-values-for-a-group-by%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







            TbP8C9x,eglx7i,P,qeU Z2rmZPJIJGHb 7pZeYlSp,dG,BdqRhU227dhY2qsxDPShbu PPCcVGvj 5rz
            B,mpHz1 yltV X,FPNze50OkDUK rBMx1

            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas