Grouping MySQL query by date
Working on a statistics plugin I needed a way to group records by the day. Turns out it's remarkably easy.
SQL:
-
SELECT COUNT(id), theDate FROM TABLE GROUP BY YEAR(theDate), MONTH(theDate), DAY(theDate);
If you just want to group by year and month take off the DAY() part. This would also be a good query to use WEEK() on to pull records grouped by the week they were posted.
Post Info
| Posted | Tuesday, July 7th, 2009 at 2:12 pm |
| Tags | Tags: mysql, php, Programming, tips |