mysql count multiple types with a single query

Question

I have a table "users" where each user has a column "status" containing the value "1" or "2" or "3";

Now with a single mysqli query, I just want to count how many users with status = 1, how many users with status = 2, and how many users with status = 3

NB: The query must be count ONLY and no data has to selected or output except the number of each status.

I found other questions here covering nearly the same topic but they are not exactly the same.

Thanks.

How come the best answers are often found in the comments section? :-) — Thorsten Kettner
– Thorsten Kettner, Commented Jun 24, 2016 at 12:33

Jan · Accepted Answer · 2016-06-24 12:30:12Z

5

How about this:

SELECT 
  count(case when status = 1 then 1 else null end) AS STATUS_1,
  count(case when status = 2 then 1 else null end) AS STATUS_2,
  count(case when status = 3 then 1 else null end) AS STATUS_3
FROM users;

count will only count non-null entries and case-when will work on other databases as well.

answered Jun 24, 2016 at 12:30

Jan

13.9k3 gold badges35 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

medk Over a year ago

Thanks for that but shall I use $row = $result->fetch_assoc(); or what as it didn't work?

Markus Winand · Accepted Answer · 2016-06-24 12:30:53Z

2

This can be done by selectivity counting: just use a CASE expression inside count that only passed a non-null value for the desired rows:

SELECT count(CASE WHEN status = 1 THEN 1 END) status_1
     , count(CASE WHEN status = 2 THEN 1 END) status_2
     , count(CASE WHEN status = 3 THEN 1 END) status_3
  FROM users

This is the same as the nicer, but not nativley supported syntax using FILTER:

SELECT count(*) FILER(WHERE status = 1) status_1
     , count(*) FILER(WHERE status = 2) status_2
     , count(*) FILER(WHERE status = 3) status_3
  FROM users

Read more about this on my website modern SQL: http://modern-sql.com/feature/filter

answered Jun 24, 2016 at 12:30

Markus Winand

8,8541 gold badge38 silver badges49 bronze badges

Comments

Nanne · Accepted Answer · 2016-06-24 12:29:53Z

1

Use something like this:

SELECT
SUM(IF(value=1,1,0)) as one,
SUM(IF(value=2,1,0)) as two,
SUM(IF(value=3,1,0)) as trhee
FROM users

The IF gives only a '1' back when your values is what you want it to be.

answered Jun 24, 2016 at 12:29

Nanne

64.4k16 gold badges122 silver badges166 bronze badges

Collectives™ on Stack Overflow

mysql count multiple types with a single query

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related