Getting zero value from empty days

Question

I need to return the count of item sold in last 30 days and return 0 if there is no item sold. My current query is working well for getting days where there is item sold but it is not returning 0 values and I need it.

Here is my current query

SELECT DATE_FORMAT(FROM_UNIXTIME(purchase_date), '%m/%d') AS Dates, COUNT(*) AS COUNT 
FROM purchases
WHERE FROM_UNIXTIME(purchase_date) BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
GROUP BY Dates
ORDER BY Dates ASC

The actual result are:

02/15 -> 10
02/16 -> 12
02/18 -> 22

But I'm missing the empty date(s) that I want to return as 0.

This is the table structure:

userid int(11), purchase_item int(11), purchase_date int(11)

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Feb 23 '21 at 09:18
The way I see it, you'll need a calendar table or at least a query to generate dates then `left join` it to your query. — FanoFN, Feb 23 '21 at 09:29
i had a friend that was able to do it in a single query but unfortunately he is dead now and i lost that query — Mireille28, Feb 23 '21 at 09:40
Oh no, I'm sorry for your loss. Take a look here for some ideas https://stackoverflow.com/questions/58425598/count-saturdays-before-15-date-as-working-days-and-skip-others — FanoFN, Feb 24 '21 at 00:06

score 3 · Answer 1 · answered Feb 23 '21 at 12:41

You can generate the last 30 days using a recursive CTE:

with recursive dates as (
      select curdate() as dte, 1 as n
      union all
      select dte - interval 1 day, n + 1
      from dates
      where n < 30
     )

The rest is just incorporating this into a query using a LEFT JOIN or correlate subquery:

with recursive dates as (
      select curdate() as dte, 1 as n
      union all
      select dte - interval 1 day, n + 1
      from dates
      where n < 30
     ) 
select d.dte, count(p.purchase_date)
from dates d left join
     purchases p
     on from_unixtime(purchase_date) >= d.dte and
        from_unixtime(purchase_date) < d.dte + interval 1 day
group by d.dte;

Getting zero value from empty days

1 Answers1