I’m trying to group and order the number of sales made in each day from a single ‘sales’ table with a created_at
column and an id
column. Each of the records might be created through out the day at any time. I’ve managed to do this with the following query:
x
SELECT date_trunc('day', created_at::date) AS period, COUNT(id)
FROM sales
GROUP BY period
ORDER BY period
However, the days with 0 sales are not shown up. Any ideas? Most of the answers I’ve found use LEFT JOIN but I can’t seem to get it to work, so I might seem to be misunderstanding how to use it. 🙁
Thank you in advance!
Advertisement
Answer
Create a temporary table that returns the required dates and then join to it
DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME
SET @StartDateTime = '2015-01-01'
SET @EndDateTime = '2015-01-12';
WITH DateRange(DateData) AS
(
SELECT @StartDateTime as Date
UNION ALL
SELECT DATEADD(d,1,DateData)
FROM DateRange
WHERE DateData < @EndDateTime
)
SELECT DateRange.DateData, Count(sales.id)
FROM sales
right join DateRange on sales.date = DateRange.DateData
group by DateRange.DateData
OPTION (MAXRECURSION 0)