How can I show unique date and count() records MySQL?

I have table with following structure and records.

table t1

+------+-------------+---------------------+
| name |  f_inserted | timestamp           |
+------+-------------+---------------------+
| AAA  |   1         | 2012-04-10 06:44:19 |
| BBB  |   0         | 2012-04-10 08:44:19 |
| AAA  |   1         | 2012-04-10 09:44:19 |
| CCC  |   1         | 2012-04-11 09:48:19 |
+------+-------------+---------------------+

I want to show unique date and count f_inserted where f_inserted = 1

In the folowing table I want the result as -

result

+-------------+------+
| date        |  cnt |
+-------------+------+
| 2012-04-10  |   2  | 
| 2012-04-11  |   1  |
+------+------+------+

Thanks in advance !

Answers


This is a basic MySQL GROUP BY query. The only tricky part is that your timestamp has a DATETIME, and you only want a date. Use the DATE() function to convert.

SELECT date(`timestamp`) as `date`, COUNT(*) from t1
WHERE f_inserted = 1
GROUP BY date(`timestamp`)

Need Your Help

How to NSLog to shell console

macos bash shell

I wrote a command line utility. I am printing to system console using:

Disable macro buttons when sheet is protected?

excel vba excel-vba

I am using shapes as macro buttons in Excel.