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.

Answer 2

If you would like to count records that reside under the TIMESTAMP field, you could construct a query that counts records for every year and every month. Below is one good suggestion on how to go about this and some notes when dealing with different SQL databases in terms of usage.

Should you wish to know for example the number of records for each year and at the same time for each month, the following query can be used in order to achieve the above. The exact functions to be used will depend on the SQL dialect that you are using.

SELECT YEAR(record_date) AS year,

       MONTH(record_date) AS month,

       COUNT(id) AS record_count

FROM stats

GROUP BY YEAR(record_date), MONTH(record_date)

ORDER BY year, month;