CODEWITHSUNDEEP
postgresqlhavingboolean-operations
Michael Wong
Michael Wong

Reputation: 115

sql: how to select a row with a true value from a column of boolean values after the HAVING clause

HI have 3 product tables, each with 3 columns namely customer name, and boolean optout and blacklist. After the Having clause, there will be 3 rows for each customer name (assuming he has all 3 products).

How do I output a true if any of the boolean columns contains a true. I figured out by using the cast operation below, but think there should be a more elegant solution.

SELECT customer_name,
       cast(int4(sum(cast(optout     As int4))) As Boolean) As optout, 
       cast(int4(sum(cast(blacklist  As int4))) As Boolean) As blacklist
FROM
(SELECT * FROM product1
UNION SELECT * FROM product2
UNION SELECT * FROM product3) AS temp1
GROUP BY customer_name, optout, blacklist
HAVING optout=true or blacklist=true;

Upvotes: 1

Views: 11005

Answers (2)

mu is too short
mu is too short

Reputation: 434665

Try the bool_or aggregate function, sounds like exactly what you're looking for:

SELECT customer_name,
       bool_or(optout)    As optout,
       bool_or(blacklist) As blacklist
FROM
(SELECT * FROM product1
UNION SELECT * FROM product2
UNION SELECT * FROM product3) AS temp1
GROUP BY customer_name, optout, blacklist
HAVING optout=true or blacklist=true;

Upvotes: 3

Myles J
Myles J

Reputation: 2880

If I have understood the question correctly I think you just need a CASE statement in the SELECT e.g.

CASE
WHEN blackLIST = TRUE OR optout = TRUE THEN 1
ELSE 0
END

Upvotes: 0

Related Questions