I am trying to return a result that shows for each month between a set of dates a count of associated records for each of the months.
The query I have written works fine except that it doesn't include any months with zero records. If the query was involving a join then I believe I could use a LEFT JOIN to ensure that all records are returned, however this query is just on 1 table.
Does anyone know how I can include rows with no records?
The kind of result I would want would be:
January 2008 6
February 2008 3
March 2008 0
April 2008 2
With my current SQL the March row would not be returned because it has a zero count.
SELECT DATENAME(Month,DateAdded) AS 'Month', Year(DateAdded) As 'Year', Count(id) As 'Total'
WHERE (dateAdded >= '10/01/2007') and (dateAdded <= '05/30/2008')
GROUP BY Year(DateAdded), month(DateAdded), DATENAME(Month,DateAdded)