List dates from specific mysql table and return 0 if day does not exists
I’m trying to generate a result from a query that list the last 7 days from today (2020/07/15) and the views matching a specific code. If in that day the code has no views, I want the day to return 0.
Table Format
DAY | CODE | VIEWS 2020-07-10 | 123 | 5 2020-07-11 | 123 | 2 2020-07-12 | 123 | 3 2020-07-15 | 123 | 8 2020-07-15 | 124 | 2 2020-07-15 | 125 | 2 Expected result from code 123
DAY | VIEWS 2020-07-09 | 0 2020-07-10 | 5 2020-07-11 | 2 2020-07-12 | 3 2020-07-13 | 0 2020-07-14 | 0 2020-07-15 | 8 I already found a way to generate the calendar dates from here and adjust to my needs, but I don’t know how to join the result with my table.
select * from (select adddate(NOW() - INTERVAL 7 DAY, t0) day from (select 1 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7) t0) v Any help would by apreceated.
One option uses a recursive query – available in MySQL 8.0:
with recursive cte as ( select current_date - interval 6 day dt union all select dt + interval 1 day from cte where dt < current_date ) select c.dt, coalesce(sum(t.views), 0) views from cte left join mytable t on t.day = c.dt group by c.dt order by c.dt You can also manually build a derived table, as you originaly intended to (this would work on all versions of MySQL):
select current_date - interval d.n day dt, coalesce(sum(t.views), 0) views from ( select 0 n union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 ) d left join mytable t on t.day = current_date - interval d.n day group by d.n order by d.n desc 
