MySQL Query GROUP BY day / month / year

Barrocal_2274 0
Viewed 809782

Is it possible to 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!

group-by date datetime mysql sql
16 Answers
codelogic_434270

If you want to get a monthly statistics with row counts per month of each year ordered by latest month, then try this:

SELECT count(id),
      YEAR(record_date),
      MONTH(record_date) 
FROM `table` 
GROUP BY YEAR(record_date),
        MONTH(record_date) 
ORDER BY YEAR(record_date) DESC,
        MONTH(record_date) DESC
user_30197990

I wanted to get similar data per day, after experimenting a bit, this is the fastest I could find for my scenario

SELECT COUNT(id)
FROM stats
GROUP BY record_date DIV 1000000;

If you want to have it per month, add additional zeroes (00) I would not recommend this from "make the code readable" perspective, it might also break in different versions. But in our case this took less then half the time compared to some other more clearer queries that I tested.

This is a MySQL answer (as MySQL is tagged in the question) and well documented in the manual https://dev.mysql.com/doc/refman/8.0/en/date-and-time-type-conversion.html

user_27162180

try it

GROUP BY YEAR(record_date), MONTH(record_date)

Admin1

Here's one more approach. This uses [MySQL's LAST_DAY() function][1] to map each timestamp to its month. It also is capable of filtering by year with an efficient range-scan if there's an index on record_date.

  SELECT LAST_DAY(record_date) month_ending, COUNT(*) record_count
    FROM stats
   WHERE record_date >= '2000-01-01'
     AND record_date <  '2000-01-01' + INTERVAL 1 YEAR
   GROUP BY LAST_DAY(record_date)

If you want your results by day, use DATE(record_date) instead.

If you want your results by calendar quarter, use YEAR(record_date), QUARTER(record_date).

Here's a writeup. https://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/ [1]: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_last-day

user_2056080
Related