Postgres group by having at least 1 row with status='started'

Question

I need to perform a group by and only including if at least one observation matches the constraint. Like I have tried to describe below.

SELECT {some variables}
FROM my_table
GROUP BY A
HAVING {at least 1 row with status='started'}

My initial guess to replace was "at least 1 row with status='started'" was "bool_or(status='started')". Which doesn't work.

Do any of you have a sugestion?

EDIT:

SELECT {some variables}
FROM my_table
GROUP BY A
HAVING bool_or(status='started');

Works fine. The reason it not work for me, was that I used the wrong quotation mark. Thanks for your help and sorry for the inconvenience.

score 2 · Accepted Answer · answered Feb 20 '20 at 12:03

2

having bool_or(status = 'started') is a nice option in Postgres, and probably your best pick here.

It is actually equivalent to:

having max( (status = 'started')::int ) = 1

For the sake of completness: in standard SQL, you would phrase this with a case expression:

having max(case when status = 'started' then 1 end) = 1

answered Feb 20 '20 at 12:03

GMB

or in modern standard SQL: `having count(*) filter (where status = 'started') > 0` – Feb 20 '20 at 12:09
@a_horse_with_no_name: is that part of the ANSI standard now? I am asking because Postgres is the only database that I know that supports such syntax. – GMB Feb 20 '20 at 12:11
Yes, it was added in SQL:2006 – Feb 20 '20 at 12:12
@a_horse_with_no_name: OK, thanks for information. It's a shame that such document are not freely available... And a shame also that database products do not put real effort in implementing such valuable part of it! – GMB Feb 20 '20 at 13:52

1 Answers1