sql – MySQL Query GROUP BY day / month / year

sql – MySQL Query GROUP BY day / month / year

GROUP BY YEAR(record_date), MONTH(record_date)

Check out the date and time functions in 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 cant really tell fast from slow until you see the difference.) Also, as time goes on, changes could be made to MySQLs 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.

sql – MySQL Query GROUP BY day / month / year

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).