Tutorial :Mysql count columns of users, and group by modified date



Question:

There are 2 columns that I want data from. Basically I want to see new signups per day. Go easy on me, my first day with mysql (or any DB for that matter)

So far I have this

mysql> select created, count(id) from user group by created;  +---------------------+-----------+  | created             | count(id) |  +---------------------+-----------+  | 2009-05-27 00:58:56 |         1 |   | 2009-05-27 01:27:30 |         1 |   | 2009-05-27 02:35:37 |         1 |   | 2009-05-27 02:58:56 |         1 |   | 2009-05-27 03:36:03 |         1 |   | 2009-05-27 03:38:03 |         1 |   | 2009-05-27 03:48:16 |         1 |   | 2009-05-27 03:51:37 |         1 |   

The output I would like is

+---------------------+----------------------+  | created             | user                 |  +---------------------+----------------------+  | 2009-05-27          | 10                   |  | 2009-05-28          | 13                   |   | 2009-05-29          | 19                   |   

Any help is appreciated!

Thanks


Solution:1

select DATE(created), count(id) from user group by DATE(created);  


Solution:2

SELECT DATE(created) as date, count(1) as nrRegistered  FROM user  GROUP BY date  

Note: By counting 1 instead of id we avoid a lookup in the table and enable an index-only query


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