How to add day gaps to mysql query without calendar table

Question

I would like to receive the sum of all requests of the last 10 days grouped by date per day. If there was no request on a day, the corresponding date should appear with sumrequests = 0.

My current query (today is the date 2020-01-10):

SELECT
  count( 0 ) AS sumrequests,
  cast( requests.created_at AS date ) AS created 
FROM
  requests 
WHERE
  (
    requests.created_at
    BETWEEN ( curdate() - INTERVAL 10 DAY ) 
    AND ( curdate() + INTERVAL 1 DAY )) 
GROUP BY
  cast(requests.created_at AS date)

I then receive the following list:

sumrequests | created
--------------------------
3           | 2020-01-05
100         | 2020-01-08

But it should give back:

sumrequests | created
--------------------------
0           | 2020-01-01
0           | 2020-01-02
0           | 2020-01-03
0           | 2020-01-04
3           | 2020-01-05
0           | 2020-01-06
0           | 2020-01-07
100         | 2020-01-08
0           | 2020-01-09
0           | 2020-01-10

How can I get this without an additional calendar table.

Thanks for help!

You could use this sort of solution to generate your date range on the fly and then `JOIN` with your data: https://stackoverflow.com/questions/9295616/how-to-get-list-of-dates-between-two-dates-in-mysql-select-query — WOUNDEDStevenJones, Jan 10 '20 at 21:21
Does this answer your question? [How to get list of dates between two dates in mysql select query](https://stackoverflow.com/questions/9295616/how-to-get-list-of-dates-between-two-dates-in-mysql-select-query) — Ankit Bajpai, Jan 10 '20 at 22:02
Consider handling issues of data display in application code — Strawberry, Jan 10 '20 at 22:49

GMB · Accepted Answer · 2020-01-10T22:29:49.870

For just 10 days of data, you can simply enumerate the numbers; using this derived number table, you can generate the corresponding date range, left join it with the table and aggregate.

SELECT
    COALESCE(count(r.created_at), 0) AS sumrequests,
    CURDATE() - INTERVAL (n.i) DAY AS created 
FROM (
    select 0 i 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 union all select 10
) n
LEFT JOIN requests r
    ON  r.created_at >= CURDATE() - INTERVAL n.i DAY 
    AND r.created_at <  CURDATE() - INTERVAL (n.i - 1) DAY  
GROUP BY n.i
ORDER BY n.i DESC

Side notes:

generally you want to avoid applying functions in the join or filtering conditions, since it prevents the use of an index; I modified your filters to not use CAST()
Since we are left joining, we need to count something that is coming from the requests table, hence we use COUNT(r.created_at) instead of COUNT(0)

How to add day gaps to mysql query without calendar table

1 Answers1