SQL Monthly Summary

I have a table that contains a startdate for each item

for example:

ID - Startdate
1  - 201开发者_开发问答1-01-01
2  - 2011-02-01
3  - 2011-04-01
...

I need a query that will give me the count of each item within each month, i need a full 12 month report. I tried simply grouping by the Month(StartDate) but this doesnt give me a zero for the months with no values, in the case above, for march.

so i would like the output to be along the lines of..

Month - Count
  1      20
  2      14
  3      0
...

Any ideas?

Thanks.

SELECT A.Month, ISNULL(B.countvalue,0) Count
FROM (SELECT 1 AS MONTH
UNION 
SELECT 2 
UNION 
SELECT 3 
UNION 
SELECT 4 
UNION 
SELECT 5 
UNION 
SELECT 6 
UNION 
SELECT 7 
UNION 
SELECT 8 
UNION 
SELECT 9 
UNION 
SELECT 10 
UNION 
SELECT 11 
UNION 
SELECT 12 ) A LEFT JOIN (SELECT datepart(month,Startdate) AS Month, Count(ID) as countvalue FROM yourTable GROUP BY datepart(month,Startdate))B
ON A.month = B.month

Hope this helps

Another way to do this using SQL Server 2005+ or Oracle.

SQL Statement

;WITH q (Month) AS (
    SELECT  1
    UNION ALL
    SELECT  Month + 1
    FROM    q   
    WHERE   q.Month < 12
)
SELECT  q.Month
        , COUNT(i.ID)
FROM    q
        LEFT OUTER JOIN Input i ON MONTH(i.StartDate) = q.Month
GROUP BY
        q.Month

Test script

;WITH Input (ID, StartDate) AS (
    SELECT 1, '2011-01-01'
    UNION ALL SELECT 2, '2011-02-01'
    UNION ALL SELECT 3, '2011-04-01'
)
, q (Month) AS (
    SELECT  1
    UNION ALL
    SELECT  Month + 1
    FROM    q   
    WHERE   q.Month < 12
)
SELECT  q.Month
        , COUNT(i.ID)
FROM    q
        LEFT OUTER JOIN Input i ON MONTH(i.StartDate) = q.Month
GROUP BY
        q.Month