CODEWITHSUNDEEP
mysql
santa
santa

Reputation: 12512

Count 2 values in 1 query

I need help with writing a query (mySQL) to

  1. Total number of users in a project, and
  2. Number of users in that project that have been "disabled"

Now, I can do it separately, like this:

SELECT count(users) FROM t1 WHERE project = 3

SELECT count(users) FROM t1 WHERE project = 3 AND status = "disabled"

But there's gotta be a simple way of combining the two in one query...

Upvotes: 1

Views: 161

Answers (5)

Stu
Stu

Reputation: 15769

Select
  Project,
  Count(Users),
  Sum(Case When Status = 'Disabled' Then 1 Else 0 End)
From
  T1
Group By Project

Upvotes: 2

Witold Sosnowski
Witold Sosnowski

Reputation: 506

You can use UNION:

mysql> SELECT count(users) AS result FROM t1 WHERE project = 3 UNION
       SELECT count(users) AS result FROM t1 WHERE project = 3 AND status = "disabled"
+--------+
| result |
+--------+
|      2 |
|      1 |
+--------+

Other solution are subqueries:

mysql> SELECT (SELECT COUNT(ID) FROM t1 WHERE project = 3) AS total_users, 
       (SELECT COUNT(ID) FROM t1 WHERE project = 3 AND status = 'disabled') AS disabled_users;
+-------------+----------------+
| total_users | disabled_users |
+-------------+----------------+
|           2 |              0 |
+-------------+----------------+

Anyway, the truth about those solutions is that two separate queries will do just fine. Only on very large systems this would matter. When we look at database performance, those two will be slower than two separate queries. The only reason we would want to do that is overhead from connection to the database.

Upvotes: 2

Uwe Geuder
Uwe Geuder

Reputation: 2317

While I was typing stu gave an answer already. I had basically the same one with slightly different syntax:

select count(users), sum(if(status='disabled',1,0)) 
  from t1
  where project = 3 ;

Upvotes: 1

Dzoki
Dzoki

Reputation: 739

SELECT status, count(users) FROM t1 group by status WHERE project = 3 AND status = "disabled"

Upvotes: -1

SVD
SVD

Reputation: 4753

Well you could do something like

SELECT status, count(users) FROM t1 **group by status** WHERE project = 3

to get a resultset of status and count of how many records in that status. Add all counts together for the total, remember the count for "disabled" for the disabled count.

Upvotes: -1

Related Questions