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 — philipxy
– philipxy, Commented Jun 8, 2020 at 2: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
– dcjnk, Commented Jun 9, 2020 at 18:10

GMB · Accepted Answer · 2020-06-07 19:57:40Z

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 7, 2020 at 19:57

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dcjnk Over a year ago

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

Gordon Linoff · Accepted Answer · 2020-06-07 20:36:40Z

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.

answered Jun 7, 2020 at 20:36

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Postgres - how to get proper count with join

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related