Menu
Group By Month and Year We also use YEAR() and MONTH() functions to ensure that data is grouped and ordered by month and year numbers. In the above query, if we use date_format function in group by clause, then MySQL will sort the groups alphabetically, instead of chronologically.
You can group month and year with the help of function DATE_FORMAT() in MySQL. The GROUP BY clause is also used.
To select all entries from a particular month in MySQL, use the monthname() or month() function.
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.
For Oracle:
select EXTRACT(month from DATE_CREATED), sum(Num_of_Pictures)
from pictures_table
group by EXTRACT(month from DATE_CREATED);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With