Reputation: 45
I'm trying to return a sum of transactions amount for each day separately for a week, I done that using this query:
SELECT DATE(`TransactionTime`) as days, SUM(`TransactionAmount`) AS SalesRevenue
FROM sales
WHERE TransactionTime BETWEEN '2021-03-21' AND '2021-03-27' GROUP BY
DATE(TransactionTime)
but it is not return all days because there is some days are not exists. so I need this days to be there with zero value for sales like this:
instead of this:
Note: the top above table is made by page inspector
Upvotes: 1
Views: 157
Reputation: 1271231
There is no "keeping" values, because they are not in the table. You need to generate them. This is simple enough with a recursive CTE:
with recursive dates as (
select date('2021-03-21') as dte
union all
select dte + interval 1 day
from dates
where dte < '2021-03-27'
)
select d.dte, coalesce(sum(TransactionAmount), 0) as SalesRevenue
from dates d left join
sales s
on s.TransactionTime >= d.dte and
s.TransactionTime < d.dte + interval 1 day
group by d.dte
Upvotes: 5