Q
Problem solve Get help with specific problems with your technologies, process and projects.

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


Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close