We help IT Professionals succeed at work.

oracle and the extract function

duder78
duder78 asked
on
Hey there.

Okay, so i'm having trouble with aggregation and subqueries.  I'm slowly starting to get it but i've hit a bit of a snag.
Here's what i'm trying to do...

In a table i'm working with, there is a 'pubdate' column of type 'date'.  I need to to count the number of occurrences of books published in each month using the extract function.  I've managed to extract the month from each date but i'm not sure how to count the dates.  

SELECT EXTRACT(MONTH FROM pubdate) FROM titles;

^^ THat's how I got the month from the dates but I'm a little foggy on how to count each month occurence.  Any help would be much appreciated.
Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2012
Commented:
SELECT EXTRACT(MONTH FROM pubdate),count(*) FROM titles
group by EXTRACT(MONTH FROM pubdate);
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Maybe I'm missing something but:

SELECT EXTRACT(MONTH FROM pubdate), count(*) FROM titles group by EXTRACT(MONTH FROM pubdate);
Most Valuable Expert 2011
Top Expert 2012

Commented:
note,  this will be grouping by the month number only.  Not month/year.

So, all books published in Dec 2009, Dec 2010, Dec 2011 will be included in the December total.  Is that what you want?

Or is Dec 2010  a different set than Dec 2011?

If so,  don't use EXTRACT, use trunc


SELECT trunc(pubdate,'mm'),count(*) FROM titles
group by trunc(pubdate,'mm')
Most Valuable Expert 2011
Top Expert 2012

Commented:
you could also do it by extracting the year too and including that in the group by

SELECT extract(year from pubdate),EXTRACT(MONTH FROM pubdate),count(*) FROM titles
group by extract(year from pubdate),EXTRACT(MONTH FROM pubdate);

Author

Commented:
First one in gets it, thanks...just what I was looking for.
Most Valuable Expert 2011
Top Expert 2012

Commented:
both answers were posted in the same minute,  I'd call that simultaneous

a split would be fair
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Thanks for the suggestion sdstuber but I'm OK with 'first'.