0

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.

Eric Brandt
  • 7,886
  • 3
  • 18
  • 35
nfn
  • 39
  • 6

1 Answers1

2

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
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Hi, using your second suggestion, when I add the where clause to filter the code (where code = 123) it only returns the dates that match the code. Anyway to adjust this? Thanks – nfn Jul 15 '20 at 12:51
  • @nfn: move the filter to the `on` clause of the `join`: `left join mytable t on t.day = current_date - interval d.n day and t.code = 123` – GMB Jul 15 '20 at 12:54