3

I wrote a simple query

SELECT date, count(user) as count FROM sessions GROUP BY date;

A response was like that

enter image description here

But, I want to see every date, if date is not exist rows must be like this

2018-02-01 | 0
2018-02-02 | 0
2018-02-03 | 0
2018-02-04 | 0
2018-02-05 | 1503582
2018-02-06 | 0
2018-02-06 | 0
2018-02-08 | 5612270
...
...
...
2018-02-31 | 0

How can I should rewrite my query for getting data for every day in the moths ?

Artem Chernov
  • 856
  • 2
  • 8
  • 26
  • do you have a dates table? – Vamsi Prabhala Feb 20 '18 at 15:27
  • Have a calendar help table with all possible dates. Right join(!) with that table. – jarlh Feb 20 '18 at 15:27
  • @VamsiPrabhala, No, I have not table with date. I think its not right way for resolve this task – Artem Chernov Feb 20 '18 at 15:29
  • https://stackoverflow.com/questions/5635594/how-to-create-a-calendar-table-for-100-years-in-sql – Jacob H Feb 20 '18 at 15:30
  • @ArtemChernov - A dates table or a numbers table actually***is*** an extremely useful and valid database structure. It makes SQL simpler ***and*** more performant *(shorter code, easier to understand code, simpler to test code, improved use of indexes, etc, etc)*. Dismissing such an idea so quickly would be a mistake and a detriment to your own career growth. I strongly recommend you search the web for the various practices surrounding numbers tables and/or dates tables. – MatBailie Feb 20 '18 at 15:49
  • @Matbailie thank you for the useful advise, I will read about a dates table, I didn`t think what this solution may be is the best – Artem Chernov Feb 20 '18 at 15:58

1 Answers1

5

You need to generate a calendar first and join it's rows with table session through date column. The subquery below will generate all 1 month based from the value set in the @START_DATE variable.

SET  @START_DATE  = '2018-01-01';  -- set your starting date here

SELECT  a.`DATE`,
        COUNT(b.user) AS `COUNT`
FROM
        (
            SELECT DATE(cal.date) `DATE` 
            FROM ( 
                SELECT @START_DATE + INTERVAL xc DAY AS date 
                FROM ( 
                      SELECT @xi:=@xi+1 as xc from 
                      (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc1, 
                      (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2, 
                      (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc3, 
                      (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc4, 
                      (SELECT @xi:=-1) xc0 
                ) xxc1 
            ) cal 
            WHERE cal.date <= DATE_ADD(DATE_ADD(@START_DATE, INTERVAL 1 MONTH), INTERVAL -1 DAY)
        ) a
        LEFT JOIN sessions b
            ON a.`DATE` = b.`DATE`
GROUP   BY a.`DATE`
ORDER   BY a.`DATE`

Here's a Demo.

The subquery that generates date was borrowed from this article: Generating a Series of Dates in MySQL and modified a little bit to allow user to input the starting date.

John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 2
    The principle I agree with, but I'd suggest to the reader that they at least consider creating a materialised numbers table or calendar table, rather than deriving one each time. – MatBailie Feb 20 '18 at 15:52
  • @MatBailie I agree on your statement. – John Woo Feb 20 '18 at 15:55
  • This is hard for my understanding, but this work perfect – Artem Chernov Feb 20 '18 at 16:01
  • @artemchernov It just creates a table of dates and LEFT JOIN on to it. As for how the dates table is calculated, that's in the link. Having a permanent dates table would allow it to be simplified significantly. – MatBailie Feb 20 '18 at 16:06