Mysql query with group by?
Table users : id (int) status (int) regdate (datetime)
I want to get the results in order of regdate, with count(id) 's and group by status.
For example :
Date Count(status = 3) Count(status = 4) Count(status = 5) 2014-02-24 2 5 8 2014-02-25 2 5 8
We should have get the results in only a line per day.
Thanks too much in advance.
You can use SUM() with condition if there are limited statutes like 3,4,5,the expression in SUM() are evaluated as boolean , for n no. of statutes look at Marc B's comments
SELECT regdate, SUM(status = 3), SUM(status = 4), SUM(status = 5) FROM `table` GROUP BY regdate ORDER BY regdate