Tutorial :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?


Solution: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)  


Solution: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.


Solution:3

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


Solution:4

This might work...

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


Solution: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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »