MYSQL query: count the dates
In the table below
+-------+-----------------------+
| id | times开发者_运维问答tamp |
+-------+-----------------------+
| 1 | 2010-06-10 14:35:30 |
| 2 | 2010-06-10 15:27:35 |
| 3 | 2010-06-10 16:39:36 |
| 4 | 2010-06-11 14:55:30 |
| 5 | 2010-06-11 18:45:31 |
| 6 | 2010-06-12 20:25:31 |
+-------+-----------------------+
I want to be able to count the dates (time is ignored). So the output should be like below:
+-------+-----------------------+
| id | type | count |
+-------+-----------------------+
| 1 | 2010-06-10 | 3 |
| 2 | 2010-06-11 | 2 |
| 3 | 2010-06-12 | 1 |
+-------+-----------------------+
What would be the query for this?
This works if you can live without the id
column in the result:
SELECT DATE(timestamp) AS type, COUNT(*) AS `count`
FROM sometable
GROUP BY DATE(timestamp)
ORDER BY DATE(timestamp)
SELECT
DATE(timestamp),
COUNT(*)
FROM
My_Table
GROUP BY
DATE(timestampe)
This doesn't give you a row number for each row. I don't know if (why?) that's important.
select date(timestamp) as type, count(*)
from your_table
group by type;
This might work...
select DATE(timestamp), count(timestamp)
from _table
group by timestamp
order by count(timestamp) desc
SELECT count( DATE_FORMAT( timestamp, '%Y-%m-%d/' ) ) , DATE_FORMAT( timestamp, '%Y-%m-%d' ) FROM tablename group by DATE_FORMAT( timestamp, '%Y-%m-%d' );
You can not include the id in the select or the count will be off.
精彩评论