Selecting all months even if they're missing

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

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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).


This was first published in April 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top