Postgres - how to get proper count with join

Question

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!

Does this answer your question? [Finding duplicate values in MySQL](https://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql) — philipxy, Jun 08 '20 at 02:07
not really - because my question is not about finding duplicate values, but mainly to joining tables and getting only 1 count on a first table field based on multiple hits in another table. — dcjnk, Jun 09 '20 at 18:10

score 2 · Answer 1 · answered Jun 07 '20 at 19:57

2

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

Demo on DB Fiddle:

status | cnt
:----- | --:
close  |   0
open   |   2

answered Jun 07 '20 at 19:57

GMB

216,147
25
84
135

Thanks! this solution looks very elegant, but I wonder how the argument that Gordon Linoff makes below about the join duplicating the number of rows would affect the response time as the database grows in size – dcjnk Jun 08 '20 at 21:12

score 2 · Answer 2 · answered Jun 07 '20 at 20:36

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.

Postgres - how to get proper count with join

2 Answers2