I think I have a tough one here... :(
I am trying to get an order count by month, even when zero. Here's the problem query:
SELECT datename(month, OrderDate) as Month, COUNT(OrderNumber) AS Orders
FROM OrderTable
WHERE OrderDate >= '2012-01-01' and OrderDate <= '2012-06-30'
GROUP BY year(OrderDate), month(OrderDate), datename(month, OrderDate)
What I'm looking to get is something like this:
Month Orders
----- ------
January 10
February 7
March 0
April 12
May 0
June 5
...but my query skips a row for March and May. I've tried COALESCE(COUNT(OrderNumber), 0) and ISNULL(COUNT(OrderNumber), 0) but I'm pretty sure the grouping is causing that not to work.