Posted By: Anonymous
I am using Oracle SQL Developer.
I essentially have a table of pictures that holds the columns:
and if I do a select *, I would get an output similar to:
01-May-12 12 02-May-12 15 03-May-12 09 ... ... 01-Jun-12 20 ... etc.
I am trying to aggregate these sums of pictures into MONTHLY numbers instead of DAILY.
I’ve tried doing something like:
select Month(DATE_CREATED), sum(Num_of_Pictures)) from pictures_table group by Month(DATE_CREATED);
This outputs an error:
ORA-00904: "MONTH": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 5 Column: 9
Do I have the Month function wrong?
I would be inclined to include the year in the output. One way:
select to_char(DATE_CREATED, 'YYYY-MM'), sum(Num_of_Pictures) from pictures_table group by to_char(DATE_CREATED, 'YYYY-MM') order by 1
Another way (more standard SQL):
select extract(year from date_created) as yr, extract(month from date_created) as mon, sum(Num_of_Pictures) from pictures_table group by extract(year from date_created), extract(month from date_created) order by yr, mon;
Remember the order by, since you presumably want these in order, and there is no guarantee about the order that rows are returned in after a group by.