How Can I Use MySQL's GROUP BY Clause to Count Records by Day, Month, or Year?

Counting MySQL Records by Day, Month, or Year using GROUP BY

This guide demonstrates how to use MySQL's GROUP BY clause with date functions to efficiently count database records grouped by day, month, or year. We'll assume a TIMESTAMP field named record_date is used to store timestamps.

To count records for a specific year, for example, 2009, use this query:

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

This query groups the results by the year extracted from record_date.

To obtain monthly counts within a given year, use this refined query:

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

This groups results by both year and month, providing a more granular monthly breakdown. Note the addition of YEAR(record_date) and MONTH(record_date) to the SELECT statement for clarity.

For daily counts, a similar approach can be applied using DAY(record_date). Consult the official MySQL documentation for a comprehensive list of date and time functions to further customize your queries.

The above is the detailed content of How Can I Use MySQL's GROUP BY Clause to Count Records by Day, Month, or Year?. For more information, please follow other related articles on the PHP Chinese website!