I would like to show monthly/weekly data for every period even if the aggregated(count) data is 0, but couldn’t find a way to do it.
We had a similar situation, where we wanted to show zero sales for a day for a given product. We solved it by using a calendar table in our SQL. This calendar table has one row in it for every day. It also has extra attributes, such as day of week, day of year, week of year, day name (sun, mon, etc), month name, month abbr, month id (1-12), etc.
so our sql had a left outer join from the calendar table to the sales table, such as
select
c.date,
sum(coalesce (s.sales_qty, 0)) as sales_qty
from calendar c
left outer join sales s on c.date = s.sales_date
group by c.date
by using the date field from the calendar table, we always had a row of data for each day of the period. and using the coalece to convert null values (where there weren’t any sales) to zeros, we had the desired results.
1 Like
wow thank you so much for the solution. i do find a calendar table in the redshift cluster we are using and that perfectly returns all the zeros. Thx!
Hi,
I have a similar problem but in my case I need to do a count instead of sum. In your example what if you had to show the number of transactions per day and you wanted the days with no transactions to show 0? How would you do that? I have access to a calendar table in my database but the count seems to make the problem more difficult.
Nvm, I think I figured it out. I have to change the order of the functions - count first and then coalesce.
coalesce(count(s.order_id), 0) as orders
1 Like