MySQL Query GROUP BY day / month / year

1.3K 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:

SELECT COUNT(id)
FROM stats
WHERE record_date.YEAR = 2009
GROUP BY record_date.YEAR

Or even:

SELECT COUNT(id)
FROM stats
GROUP BY record_date.YEAR, record_date.MONTH

To have a monthly statistic.

Thanks!

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.

Example:

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

MySQL Query GROUP BY day / month / year

Your Answer