think123 think123 - 6 months ago 11
MySQL Question

How to add a condition in GROUP BY based on the number of rows with a certain column value?

I have this table:

-------------------------
| id | user_id | status |
-------------------------
| 1 | 1 | 1 |
-------------------------
| 2 | 1 | 0 |
-------------------------
| 3 | 1 | 1 |
-------------------------
| 4 | 2 | 1 |
-------------------------
| 5 | 2 | 0 |
-------------------------


Basically I want to select all the users (grouped by
user_id
obviously), where the number of
1
statuses and the number of
0
statuses are not the same. For the above sample data, it would return me
user_id
1 only, as user 1 has 2
1
statuses, and 1
0
status. It would not return me
user_id
2, as user 2 as 1
1
status, and 1
0
status, effectively cancelling each other out.

I was thinking of some condition which allowed me to use
COUNT
with a condition, but I'm not sure how that can be done.

Answer

You need to use the HAVING() clause with CASE EXPRESSION

SELECT t.user_id
FROM YourTable t
GROUP BY t.user_id
HAVING COUNT(CASE WHEN t.status = 1 THEN 1 END) <>
       COUNT(CASE WHEN t.status = 0 THEN 1 END)

This will basically check for each group, that the count of status = 1 is different from the count of status = 0

MySQL allows Boolean expression , so the HAVING() clause could be written as:

HAVING sum(t.status = 1) <> sum(t.status = 0)