duder78
asked on
oracle and the extract function
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(
group by trunc(pubdate,'mm')
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(
group by trunc(pubdate,'mm')
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);
SELECT extract(year from pubdate),EXTRACT(MONTH FROM pubdate),count(*) FROM titles
group by extract(year from pubdate),EXTRACT(MONTH FROM pubdate);
ASKER
First one in gets it, thanks...just what I was looking for.
both answers were posted in the same minute, I'd call that simultaneous
a split would be fair
a split would be fair
Thanks for the suggestion sdstuber but I'm OK with 'first'.
SELECT EXTRACT(MONTH FROM pubdate), count(*) FROM titles group by EXTRACT(MONTH FROM pubdate);