Selecting all months even if they're missing
I have a table of information like this:
table1 datefield part 2003-01-01 a 2003-02-22 a 2003-02-21 a 2003-04-12 a 2003-04-21 a 2003-04-30 b 2003-06-01 a 2003-12-01 a
If I use the query:
SELECT MONTH(DATEFIELD) AS MTH, COUNT(*) AS QTY FROM TABLE1 WHERE PART='a' GROUP BY MONTH(DATEFIELD)
I get:
MTH QTY 1 1 2 2 4 2 6 1 12 1
I would like to include all the months in my query regardless of the fact if the count for a particular month is 0. I would like to get:
MTH QTY 1 1 2 2 3 0 4 2 5 0 6 1 7 0 8 0 9 0 10 0 11 0 12 1
The integers table comes to the rescue again!
select i as mth , count(part) as qty from integers left outer join table1 on i = month(datefield) and part='a' where i between 1 and 12 group by i
If you don't have an integers table yet, you can see how to create one in Aggregates for date ranges (4 October 2002).