Include zero counts for grouping date ranged based SQL query

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:

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!

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)