Wednesday, December 19, 2018

phpMyPassion

MySQL Group By Day, Month Or Year


MySQL Group By Day, Month Or Year

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")





About Author -

Hi, I am Anil.

Welcome to my eponymous blog! I am passionate about PHP programming. Here you will find a hug of information on web development, web design, PHP and Latest technology.

Subscribe to this Blog via Email :