CODEWITHSUNDEEP
sqlpostgresql
cvzx
cvzx

Reputation: 17

counting records from two tables

I have two tables in tab1 and tab2, goal is to count all occurrences in tab2 and display them next to id which are present in tab1;

tab1 looks:

id
A
B
C
D

tab2:
in  out
A   B
A   B
    C
D   

    D
C

i'd like to get:

id  countIN countOUT
A   2   0
B   0   2
C   1   1
D   1   1

i'am trying with this:

select k.id, count(t.in)  from tab1 k left outer join tab2 t on t.in= k.id group by k.id;

and it's working only for countIN, i dont have any idea how to do it for countOUT

Upvotes: 0

Views: 52

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

I recommend joining to two subqueries, each of which aggregates either the in or out columns:

SELECT
    t1.id,
    COALESCE(t2.countIN, 0) AS countIN,
    COALESCE(t3.countOUT, 0) AS countOUT
FROM tab1 t1
LEFT JOIN
(
    SELECT "in", COUNT(*) AS countIN
    FROM tab2
    GROUP BY "in"
) t2
    ON t1.id = t2."in"
LEFT JOIN
(
    SELECT out, COUNT(*) AS countOUT
    FROM tab2
    GROUP BY out
) t3
    ON t1.id = t3.out
ORDER BY
    t1.id;

enter image description here

Demo

Upvotes: 0

realnumber3012
realnumber3012

Reputation: 1062

try this query

SELECT id, 
(SELECT COUNT(*) FROM tab2 t2 WHERE t2.in = t1.id) AS countIn, 
(SELECT COUNT(*) FROM tab2 t2 WHERE t2.out = t1.id) AS countOut
FROM tab1 t1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I like lateral joins for this:

select v.id, sum(t2.ins) as ins, sum(t2.outs) as outs
from tab2 t2 left join lateral
     (values (t2.in, 1, 0), (t2.out, 0, 1)) v(id, ins, outs)
group by v.id;

This doesn't use tab1, so it won't return 0 counts (in both columns). If you need both:

select t1.id, sum(v.ins) as ins, sum(v.outs) as outs
from tab1 t1 left join
     (tab2 t2 left join lateral
      (values (t2.in, 1, 0), (t2.out, 0, 1)) v(id, ins, outs)
     )
     on t1.id = v.id
group by t1.id;

Upvotes: 0

Related Questions