I have a table called 'apple' and I wrote the following query:
select name,
count(name),
case when istasty is null then false else istasty end
from apple
group by name, istasty;
This the output:
I am trying to group the name
and istasty
flag using following condition:
tala
has both true
and false
istasty
column. However, I want to return false because it has atleast one false istasty
column. istasty
column for a particular name column is true
then return true
; Similarly, if all istasty
column is false
, then return false
for that particular name column.I can achieve using the bool_and
operator in postgresql
by writing the query:
select name, count(name), bool_and(coalesce(istasty = true, false)) from apple group by name;
Is there any way I can modify my first query so that I add filter in having clause
to achieve the same result as I got using bool_and
operator in second query? Or is there any other possible way?
Please note I am not using bool_and
operator. I appreciate your time. Thank you.
An alternative to using the bool_and
operator would be regular conditional aggregation:
SELECT
name,
COUNT(*) AS count,
CASE WHEN SUM(CASE WHEN !istasty THEN 1 ELSE 0 END) > 0
THEN FALSE ELSE TRUE END AS result
FROM apple
GROUP BY name;
I am not sure what you have in mind when you suggest using the HAVING
clause. By moving the conditional check to the HAVING
clause you may exclude/include specific groups in the query matching the condition.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With