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;
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;
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
精彩评论