MySQL Group By Day, Month Or Year

Table of Contents

Most frequently occurred problem that is faced by developers. Here I am gonna explain you for the query Group by Day, Month or Year in MySQL.

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 DATE_FORMAT(created_at,"%Y-%m-%d")

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

Similarly Group by month:

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

Similarly Group by year:

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

Related posts