MySQL query: show number of entries by day
I have a table with structure:
id|name| date
---------------------------
1|qwer|2011-08-29 13:11:59
2|wert|2011-08-29 13:11:59
3|erty|2011-08-30 13:11:59
4|rtyu|2011-08-31 13:11:59
I need a query to show开发者_Go百科 data like this:
day |number_of_entries
----------------------------
2011-08-29| 2
2011-08-30| 1
2011-08-31| 1
Using the GROUP BY
function and the COUNT
function gives the following:
SELECT DATE(`date`), COUNT(*) AS number_of_entires
FROM tablename
GROUP BY DATE(`date`)
ORDER BY `date`
GROUP BY - MySQL Reference Manual
DATE - MySQL Reference Manual
SELECT DATE(`date`) AS day,
COUNT(*) AS number_of_entries
FROM mytable
GROUP BY DATE(`date`)
Use MySQL's DATE()
function.
精彩评论