MySQL Query GROUP BY day / month / year
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.
try this one
SELECT COUNT(id)FROM statsGROUP 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).