How to query GROUP BY Month in a Year

Solution 1:

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.

Solution 2:

For Oracle:

select EXTRACT(month from DATE_CREATED), sum(Num_of_Pictures)
from pictures_table
group by EXTRACT(month from DATE_CREATED);