MySQL group by day, month or year

Using the DATE_FORMAT operator, you can easily group the timestamp, date or datetime column using any format you want.
For example, I needed to group rows that were added on the same day. Here is my query:

select count(*), DATE_FORMAT(created_at,"%Y-%m-%d") as created_day FROM widgets GROUP BY created_day

This query will give you result like this :

count(*) | created_day
126 | 2012-04-12
168 | 2012-04-13
169 | 2012-04-14
189 | 2012-04-15
187 | 2012-04-16
131 | 2012-04-17

Group by month:

select count(*), DATE_FORMAT(created_at,"%Y-%m") as created_month FROM widgets GROUP BY created_month

Group by year:

select count(*), DATE_FORMAT(created_at,"%Y") as created_year FROM widgets GROUP BY created_year
Share this Post:
Digg Google Bookmarks reddit Mixx StumbleUpon Technorati Yahoo! Buzz DesignFloat Delicious BlinkList Furl

Comments are closed.