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

No Responses to “MySQL group by day, month or year”

Leave a Reply:

Name (required):
Mail (will not be published) (required):
Comment (required):
XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>