CODEWITHSUNDEEP
sql
uksz
uksz

Reputation: 18699

How to check condition in group by expression?

I have a database of the following schema (columns):

id    phoneNumber    isActive

Now, there can be multiple records with the same phoneNumber. I would like to check, if for any of the records with the same phoneNumber, there exists one that has property isActive set to true. If it does, I don't want to see it in the result.

SELECT * FROM TABLE WHERE isActive = 'true' GROUP BY phoneNumber;

This doesnt work -> it returns the records even though one has isActive set to true

Upvotes: 0

Views: 133

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can do this using a HAVING clause:

SELECT phoneNumber
FROM TABLE
GROUP BY phoneNumber
HAVING SUM(CASE WHEN isActive = 'true' THEN 1 ELSE 0 END) = 0;

Note: Using SELECT * with GROUP BY is not valid SQL and not allowed in almost all databases. It also shows sloppy thinking. What should be values be for the columns that are not in the GROUP BY clause? Those columns should be arguments to aggregation functions, such as MIN(), MAX(), or SUM().

Upvotes: 2

Related Questions