MySQL Query GROUP BY day / month / year

Question

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!

Answer 1

Try this code:

GROUP BY YEAR(Record_date), MONTH(Record_date)

OR

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

If you want to prepare for SQL developer jobs then go through this My SQL Interview Questions.