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.

Answers


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

Need Your Help

Extending windows explorer context menu

c# windows registry associations contextmenu

I'm having hard times figuring out why this doesn't work on my computer. I've read this article http://msdn.microsoft.com/en-us/library/bb776820.aspx and tried it, and it works for an unknown file ...

PHP MYSQL SUM Until a row then stop

php mysql sum case

Basically I am creating a sort of checking account type query.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.