MySQL Query GROUP BY day / month / year
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!
GROUP BY YEAR(record_date), MONTH(record_date)
查看MySQL中的日期和时间函数。
GROUP BY DATE_FORMAT(record_date, '%Y%m')Note (primarily, to potential downvoters). Presently, this may not be as efficient as other suggestions. Still, I leave it as an alternative, and a one, too, that can serve in seeing how faster other solutions are. (For you can't really tell fast from slow until you see the difference.) Also, as time goes on, changes could be made to MySQL's engine with regard to optimisation so as to make this solution, at some (perhaps, not so distant) point in future, to become quite comparable in efficiency with most others.
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)
上一篇: 从日期获取月份名称
下一篇: MySQL查询GROUP BY日/月/年