Search code examples
sqlpostgresqlgroup-byhaving-clause

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

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.

Solution

  • 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