MySQL apply multiple conditions in one query

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

score 3 · Answer 1 · answered Feb 20 '15 at 08:58

3

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

answered Feb 20 '15 at 08:58

juergen d

201,996
37
293
362

That was fast, and it works! Thanks – NVG Feb 20 '15 at 08:59
Query1 time = 0.0395s on a table with 50k users; Query2 time = 0.0250s on 2nd query on a table with 50k users – NVG Feb 20 '15 at 09:05
these queries return 2 unnecessary columns for each status – ASh Feb 20 '15 at 09:05
when status = '0' they will return row '0', some_count, 0, 0; when status = '1' they will return row '1', 0, some_count, 0 – ASh Feb 20 '15 at 09:08

ASh · Accepted Answer · 2015-02-20T09:06:38.333

2

query returns count of statuses in 1 row

select
  sum(case when status = '0' then 1 else 0 end) as c1,
  sum(case when status = '1' then 1 else 0 end) as c2,
  sum(case when status = '2' then 1 else 0 end) as c3
from users 
where status = '0' or status = '1' or status = '2'

edited Feb 20 '15 at 09:06

answered Feb 20 '15 at 08:59

ASh

34,632
9
60
82

Query time = 0.0240s on a table with 50k users. It seems to be more efficient when using WHERE rather than GROUP BY – NVG Feb 20 '15 at 09:06
1

There's a mistake in your query btw ... replace 2nd "when" with "then" – NVG Feb 20 '15 at 09:08

MySQL apply multiple conditions in one query

2 Answers2