MySQL apply multiple conditions in one query [duplicate]

Question

I have a table named users that contains the following columns:

id
login
status

Now I want to create a page of statistics in PHP and I want to see how many users have status=0, how many users have status=1, how many users have status=2.

I want to do this the most efficient possible way, without having to run 3 queries.

Right now I only know to do this with 3 queries in UNION:

(SELECT COUNT(*) FROM users WHERE status='0') UNION (SELECT COUNT(*) FROM users WHERE status='1') UNION (SELECT COUNT(*) FROM users WHERE status='2')

I dont know too much SQL programming but I was thinking that something like this might work:

SELECT IF(status='0',stat0++),IF(status='1',stat1++),IF(status='2',stat2++) FROM users GROUP BY status

But it doesnt work because the syntax is wrong

Answer 1

You can group by the status and sum up the different status like this.

select status, 
       sum(status = 1) as status1_count,
       sum(status = 2) as status2_count,
       sum(status = 3) as status3_count
from users
group by status

which is MySQL syntax. General SQL ANSI syntax would be

select status, 
       sum(case when status = 1 then 1 end) as status1_count,
       sum(case when status = 2 then 1 end) as status2_count,
       sum(case when status = 3 then 1 end) as status3_count
from users
group by status