How to count result values from join query in another table?

I have two tables like so:

     table1(user, id, sex)   table2(user, name, sex)
     jjj 123 m               jjj John m      
     jjj 124 m               bbb Bob  m 
     jjj 125 m               ppp Pete f 
     bbb 126 m
     bbb 127 f
     ppp 128 f
     ppp 129 m
     ppp 130 m

I want result table where it displays all the users with their names and sex from table 2 who changed their sex at some point along with a count of how many users each name has. So this would be the result table:

    (user, name, sex, count)
     bbb Bob  m 2
     ppp Pete f 3

Currently im using this query:

select table2.user, table2.name, table2.sex, count(*)
from table1
join table2 on table1.user = table2.user
where table1.sex <> table2.sex
group by table2.user, table2.name, table2.sex
order by user

However the count column just counts from the resulting join table and not from original table1. Any ideas? thanks.

Answer

If I follow this correctly, one option use a lateral join and filtering:

select t2.*, t1.cnt
from table2 t2
inner join lateral (
    select count(*) as cnt, min(sex) as minsex, max(sex) as maxsex
    from table1 t1
    where t1.user = t2.user 
) t1 on t1.minsex <> t1.maxsex or t1.minsex <> t2.sex

Basically this filters table1 on users that have different sex or whose sex is different than in table2.

Advertisement

Answer