Sorry as a newcomer to sql (postgres in this case) I can't tease out the right answer from similar questions. So here's an example
I have two tables:
records:
id | status
----------------
1 | open
2 | open
3 | close
4 | open
events:
id | record_id | role | something_else
---------------------------------------------
1 | 2 | admin | stringA
2 | 1 | user | stringB
3 | 4 | admin | stringC
4 | 2 | admin | stringD
5 | 2 | admin | stringE
6 | 2 | user | stringF
7 | 3 | user | stringG
I basically would like to have a count(status) that reflects how many records have at least one events.role = 'admin' in the events table
in the above example it would be:
status | count
---------------
open | 2
close | 0
Any help much appreciated!
No need for nested queries. You can just use conditional aggregation:
select r.status, count(distinct r.id) filter(where e.role = 'admin') cnt
from records r
inner join events e on e.record_id = r.id
group by r.status
status | cnt :----- | --: close | 0 open | 2
I basically would like to have a count(status) that reflects how many records have at least one events.role = 'admin' in the events table.
I would suggest:
select r.status, count(*) filter (where has_admin)
from (select r.*,
(exists (select 1 from events e where e.record_id = r.id and e.role = 'admin')) as has_admin
from records r
) r
group by r.status;
For your small data sample, the difference between exists and a join doesn't matter. With more data, though, the exists does not multiply the number of rows, which should make it a bit faster. Also, this guarantees that all statuses are included, even those with no events.