Fill zero dates in GROUP BY MySQL

Question

I have this query:

SELECT date AS datekey, count(*) AS total
FROM `tags` AS `t`
         INNER JOIN `thing_tags` AS `tt` ON `tt`.`tag_id` = `t`.`tag_id`
         INNER JOIN `things` AS `th` ON `tt`.`thing_id` = `th`.`thing_id`
 WHERE `t`.`other_id` = 14
  AND date(`date`) >= '2019-12-20'

GROUP BY datekey
ORDER BY datekey DESC

Which gives me these results:

2022-07-15,8
2022-07-12,16
2022-07-06,10
2022-07-01,3

What I need, is a record for every single day, even if the count is zero:

(record for every day since 2019-12-20)
2022-07-06,10
2022-07-05,0
2022-07-04,0
2022-07-03,0
2022-07-02,0
2022-07-01,3

I was hoping that I could use some kind of date function to create a structure which I can then join to but I can't figure out how to do it.

some kind of date function - there isn't one , you either need a calendar table as a helper or a cte to generate dates.https://stackoverflow.com/questions/67831719/recursive-cte-in-mysql-for-missing-dates — P.Salmon, Aug 18 '22 at 08:28

Tim Biegeleisen · Answer 1 · 2022-08-18T08:54:39.987

1

You may use a calendar table here, which is just a table containing all the dates which you want to appear in your output. Left join this calendar table to the tags table to ensure the all dates which you want to appear are in the result set.

SELECT c.date datekey, COUNT(t.thing_id) AS total
FROM calendar c
LEFT JOIN tags t ON t.date = c.date
INNER JOIN thing_tags tt ON tt.tag_id = t.tag_id
INNER JOIN things t ON tt.thing_id = t.thing_id AND t.other_id = 14
WHERE c.date >= '2019-12-20'
GROUP BY datekey
ORDER BY datekey DESC;

The calendar table used above should contain dates beginning on 2019-12-20 and continuing until whatever ending date you want. See this SO question and answers for some ideas on how to populate calendar table.

edited Aug 18 '22 at 08:54

answered Aug 18 '22 at 08:19

Tim Biegeleisen

502,043
27
286
360

Yes, that's really good. Is there any way to do it without creating/maintaining a table? Maybe a temporary table? – Mick Aug 18 '22 at 08:28
@Mick If you check the link at the bottom of my answer, it will give a few ways to do that. Best bet for long term performance would be in fact to maintain a dedicated table. – Tim Biegeleisen Aug 18 '22 at 08:29
1

Note that the `t.other_id = 14` condition needs to be moved from WHERE to ON, to get the wanted LEFT JOIN result. – jarlh Aug 18 '22 at 08:53
Also, `t` is defined twice, both for tags and things. – jarlh Aug 18 '22 at 10:01
Yeah. that's my bad. I just rename them so that my table names aren't public. – Mick Aug 18 '22 at 10:03
@TimBiegeleisen there is no chance that we are going to create and maintain a table, just for this one query. – Mick Aug 18 '22 at 10:04
@Mick Check the answer given by `IvanD` in the link at the bottom of my answer. You may simply include that as an inline subquery, replacing `FROM calendar c` in my answer with `FROM (...)` using the subquery by IvanD. – Tim Biegeleisen Aug 18 '22 at 10:06
Great, I'm making progress with that. Will update when I'm done. Thanks. – Mick Aug 18 '22 at 14:28

score 0 · Answer 2 · answered Aug 19 '22 at 08:13

This is how I have done it. I looked at these answers (as suggested):

How to populate a table with a range of dates?

I ended up with this which I then LEFT JOIN to my existing query:

SET @calendar_start =  '2022-07-05';
SET @calendar_end =   '2022-07-10';

SELECT CAST(( @calendar_start + INTERVAL (M + H + T + U) DAY) AS date) all_days
                                     FROM (SELECT 0 M
                                           UNION ALL
                                           SELECT 1000
                                           UNION ALL
                                           SELECT 2000
                                           UNION ALL
                                           SELECT 3000
                                           UNION ALL
                                           SELECT 4000 -- 11 years

                                          ) M
                                              CROSS JOIN

                                          (SELECT 0 H
                                           UNION ALL
                                           SELECT 100
                                           UNION ALL
                                           SELECT 200
                                           UNION ALL
                                           SELECT 300
                                           UNION ALL
                                           SELECT 400
                                           UNION ALL
                                           SELECT 500
                                           UNION ALL
                                           SELECT 600
                                           UNION ALL
                                           SELECT 700
                                           UNION ALL
                                           SELECT 800
                                           UNION ALL
                                           SELECT 900
                                          ) H
                                              CROSS JOIN
                                          (SELECT 0 T
                                           UNION ALL
                                           SELECT 10
                                           UNION ALL
                                           SELECT 20
                                           UNION ALL
                                           SELECT 30
                                           UNION ALL
                                           SELECT 40
                                           UNION ALL
                                           SELECT 50
                                           UNION ALL
                                           SELECT 60
                                           UNION ALL
                                           SELECT 70
                                           UNION ALL
                                           SELECT 80
                                           UNION ALL
                                           SELECT 90
                                          ) T
                                              CROSS JOIN
                                          (SELECT 0 U
                                           UNION ALL
                                           SELECT 1
                                           UNION ALL
                                           SELECT 2
                                           UNION ALL
                                           SELECT 3
                                           UNION ALL
                                           SELECT 4
                                           UNION ALL
                                           SELECT 5
                                           UNION ALL
                                           SELECT 6
                                           UNION ALL
                                           SELECT 7
                                           UNION ALL
                                           SELECT 8
                                           UNION ALL
                                           SELECT 9
                                          ) U
                                     WHERE (@calendar_start + INTERVAL (M + H + T + U) DAY) <= (@calendar_end)
                                     ORDER BY all_days DESC) AS cal

Fill zero dates in GROUP BY MySQL

2 Answers2