Question 109856 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How do I obtain the count of Distinct values after joining two table?

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.

Tags:

2

Answers


  1. 0
    1. Your code was missing a group by to work.
    2. With it added, you were counting all matching entries instead of distinct ones.
    3. A case statement inside an aggregate function is emulating an aggregate filter.
    4. If columns used for join have matching names, you can list them in join...using(...):
    5. 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.

    Login or Signup to reply.
  2. 0

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search