Help with MYSQL query

Question

In the table below

+-------+-----------------------+ 
| id    | timestamp             | 
+-------+-----------------------+ 
| 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?

Answer 1

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)

Answer 2

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.

Answer 3

select date(timestamp) as type, count(*)
from your_table
group by type;

Answer 4

This might work...

select DATE(timestamp), count(timestamp)
    from _table
group by timestamp
order by count(timestamp) desc

Answer 5

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.