MySQL Query GROUP BY day / month / year

999    Asked by ranjan_6399 in Data Science , Asked on Jul 15, 2021

Is it possible I make a simple query to count how many records I have in a determined period of time like a Year, month or day, having a TIMESTAMP field, like:

FROM stats
WHERE record_date.YEAR = 2009
GROUP BY record_date.YEAR

Or even:

FROM stats
GROUP BY record_date.YEAR, record_date.MONTH

To have a monthly statistic.


Answered by Ranjana Admin

Try this code to solve sql group by day:

GROUP BY YEAR(Record_date), MONTH(Record_date)


You can use DATE_FORMAT operator. If you are using this you can easily group the date, timestamp or datetime column using whatever format you want.


If you want to group rows that were added on the same day.

Use this query:

Select count(*), DATE_FORMAT(Created_At,"%Y-%m-%d") as Created_Day1 FROM Mydate1 GROUP BY Created_Day1

This query will give you the following result:

count(*) | Created_Day1

127 | 2013-05-13

169 | 2013-05-14

170 | 2013-05-15

190 | 2013-05-16

188 | 2013-05-17

132 | 2013-05-18

If you want to Group by month, then use this code:

Select count(*), DATE_FORMAT(Created_At,"%Y-%m") as Created_Month1 FROM Mydate1 GROUP BY Created_Month1

If you want to Group by year, then use this code:

select count(*), DATE_FORMAT(Created_At,"%Y") as Created_Year1 FROM Mydate1 GROUP BY Created_Year1

Your Answer


Parent Categories