Listing all Months Regardless of Values

[philiprakusen]

I have written the following query which returns the number of orders received grouped by the year and month:

SELECT DATEPART(yyyy, order_placeddate) AS year, DATEPART(mm, order_placeddate) AS month, count(order_id) AS orders
FROM orders
GROUP BY DATEPART(yyyy, order_placeddate), DATEPART(mm, order_placeddate)
ORDER BY year, month

year   month   orders      
----   -----   ------ 
2004   6       17
2004   7       37
2004   8       30
2004   9       42
2004   10      34
2004   11      46
2005   1       25
2005   2       7
2005   4       1

The obvious problem with the above is that it misses out the months that have no orders, i.e. December, March, May, etc.

Is there a way I can amend my query so that it shows all months regardless of whether any orders were placed?

I have thought about trying to LEFT OUTER JOIN the above to a table that has rows with values of 1 – 12, but I’m not convinced this is the answer... and I don’t really know how to do it!

Do let me know if any of the above is unclear – what I’m after is the following:

year   month   orders      
----   -----   ------ 
2004   6       17
2004   7       37
2004   8       30
2004   9       42
2004   10      34
2004   11      46
2004   12      0
2005   1       25
2005   2       7
2005   3       0
2005   4       1
2005   5       0

Many thanks

[andi_g69]

I approached a similar problem with a calendar table from which I left join the facts' table.