Group by, then select only records where every value of a boolean column is true

Question

I have both a MS SQL Database and a postgres database. A solution in either will work as I can translate it.

We have a customer_phone table where the relevant columns are:

id, customer_id, phone, is_bad

What I need to do is select all the customer_id's from this table that ONLY have is_bad = true. So if you have 1 good phone number and 1 bad, you shouldn't appear.

For some reason I'm struggling to find an easy way to do this, I feel like it should be deceptively simple.

This was a start that gives me at least all the customers a count of their good and bad numbers, but I'm wondering if there is a way that doesn't involve having to use subqueries and in's?

select  customer_id, is_bad, count(customer_id)
from customer_phone cp
group by customer_id, is_bad
order by customer_id desc

Answer 1

The most intuitive way I've found to add over a boolean column like this is to SUM a column which is 1 when TRUE and 0 when FALSE:

CASE WHEN some_bool THEN 1 ELSE 0 END

So in your case:

SELECT id
FROM phones
GROUP BY id
HAVING
SUM(CASE WHEN NOT is_bad THEN 1 ELSE 0 END) = 0

Check out this SQL fiddle which shows the query in action.

Answer 2

You could use the "aggregate function", bool_and.

This takes a set of inputs and returns:

true if all input values are true, otherwise false

In your case, where we want every is_bad to be true for a particular customer:

select customer_id
from customer_phone
group by customer_id
having bool_and(is_bad)