PostgreSQL getting the count of distinct values after joining tables

Question

I am not able to get the proper count. I have 2 tables:

Tbl1

id	name	call
123	aaaaaa	15
132	bbbbb	0

Tbl2

id	involvement
123	0
123	0
123	1

I would like to get the count of ids where call = 15 and involvement = 0. My query:

select t1.id,
       COUNT(CASE WHEN t1.call=15 THEN 1 END) as calls
from Tbl1 t1 
  left join Tbl2 t2 
    on t2.id = t1.id 
where t2.involvement = 0;

The result expected for count is 1.

Could you please format the question in a way that is easy to understand? — godot, May 02 '23 at 07:36
Does this answer your question? [Left Join With Where Clause](https://stackoverflow.com/questions/4752455/left-join-with-where-clause) — philipxy, May 02 '23 at 08:40

Zegarek · Answer 1 · 2023-05-02T08:54:16.063

Your code was missing a group by to work.
With it added, you were counting all matching entries instead of distinct ones.
A case statement inside an aggregate function is emulating an aggregate filter.
If columns used for join have matching names, you can list them in join...using(...):
Unless you double-quote your object names, they are folded to lower case, so Tbl1 and Tbl2 are actually tbl1 and tbl2. Some ORM's, IDE's and clients can be set to auto-quote. For clarity, it's best to use the actual name the objects end up getting.

Demo:

select count(distinct t2.id)     filter (where t1.call=15) as calls,
       array_agg(distinct t2.id) filter (where t1.call=15) as the_ids
from tbl1 t1 left join tbl2 t2 using(id)
where t2.involvement = 0;

If you also want to see a list of those distinct id that were counted, you can use the string_agg() or array_agg() shown above.

Check out Markdown help to format your questions and answers in the future.

score 0 · Answer 2 · answered May 02 '23 at 08:23

I would prefer exists-based solution, because of 2 reasons:

left join multiplicates t1.ids which must be "repaired" with distinct (unless id is not unique in t1 itself - I assume it is a key)
the intent is IMHO clearer for reader - it says any row with involvement = 0 suffices for counting. (If - for example - the logic had to be changed to, say, having only involvement = 0, the condition would be easily changed to all or not exists operator but core of query would stay untouched.)

select count(t1.id)
from Tbl1 t1
where t1.call = 15
  and exists(select 1 from Tbl2 t2 where t2.id = t1.id and t2.involvemet = 0)

PostgreSQL getting the count of distinct values after joining tables

2 Answers2