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

Complete and simple solution with similarly performing yet shorter and more flexible alternative currently active:

SELECT COUNT(*) FROM stats
-- GROUP BY YEAR(record_date), MONTH(record_date), DAYOFMONTH(record_date)
GROUP BY DATE_FORMAT(record_date, '%Y-%m-%d')

Answer 2

I tried using the 'WHERE' statement above, I thought its correct since nobody corrected it but I was wrong; after some searches I found out that this is the right formula for the WHERE statement so the code becomes like this:

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

Answer 3

If your search is over several years, and you still want to group monthly, I suggest:

version #1:

SELECT SQL_NO_CACHE YEAR(record_date), MONTH(record_date), COUNT(*)
FROM stats
GROUP BY DATE_FORMAT(record_date, '%Y%m')

version #2 (more efficient):

SELECT SQL_NO_CACHE YEAR(record_date), MONTH(record_date), COUNT(*)
FROM stats
GROUP BY YEAR(record_date)*100 + MONTH(record_date)

I compared these versions on a big table with 1,357,918 rows (innodb), and the 2nd version appears to have better results.

version1 (average of 10 executes): 1.404 seconds
version2 (average of 10 executes): 0.780 seconds

(SQL_NO_CACHE key added to prevent MySQL from CACHING to queries.)

Answer 4

try this one

SELECT COUNT(id)
FROM stats
GROUP BY EXTRACT(YEAR_MONTH FROM record_date)

EXTRACT(unit FROM date) function is better as less grouping is used and the function return a number value.

Comparison condition when grouping will be faster than DATE_FORMAT function (which return a string value). Try using function|field that return non-string value for SQL comparison condition (WHERE, HAVING, ORDER BY, GROUP BY).

Answer 5

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

Answer 6

.... group by to_char(date, 'YYYY') --> 1989

.... group by to_char(date,'MM') -->05

.... group by to_char(date,'DD') --->23

.... group by to_char(date,'MON') --->MAY

.... group by to_char(date,'YY') --->89