CODEWITHSUNDEEP
mysqlsql
medk
medk

Reputation: 9539

mysql count multiple types with a single query

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.

Upvotes: 0

Views: 1465

Answers (3)

Markus Winand
Markus Winand

Reputation: 8716

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

Upvotes: 2

Jan
Jan

Reputation: 13858

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.

Upvotes: 5

Nanne
Nanne

Reputation: 64409

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.

Upvotes: 1

Related Questions