CODEWITHSUNDEEP
mysqlsql
Hussein
Hussein

Reputation: 45

MySQL return sum of sales by days for a week with keeping empty values

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:

enter image description here

instead of this:

enter image description here

Note: the top above table is made by page inspector

Upvotes: 1

Views: 157

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions