SQLite group by all the days in selected date range even data not exsist

Question

I have a table invoice with two columns, date and total I want get last 30 days total group by all days of the month (or else last 7 days groups by all days of the week)

date     total
11/16    500
11/23    200
12/2     400
12/4     600

I have tried something like this,

SELECT strftime('%m/%d', Date) as valDay, SUM(Total) 
FROM invoice  
GROUP BY valDay 
Order By Date DESC LIMIT 30 ;

It gives last 30 records. but I want records for all last 30 days even the table have no data for some of days (it must return 0 as total for that days)

Answer 1

You can use a recursive query to generate the dates, then bring the table with a left join, and aggregate:

with recursive cte as (
    select date('now') as dt,  date('now', '-30 day') last_dt
    union all select date(dt, '-1 day'), last_dt from cte where dt > last_dt
)
select c.dt, coalesce(sum(i.total), 0) as total
from cte c
left join invoice i on i.date >= c.dt and i.date < date(c.dt, '+1 day')
group by c.dt
order by c.dt desc