How to use join with aggregate function in postgresql?

Question

I have 4 tables

Table1

id    | name    
1     | A
2     | B

Table2

id    | name1    
1     | C
2     | D

Table3

id    | name2    
1     | E
2     | F

Table4

id    | name1_id    | name2_id    | name3_id        
1     | 1           | 2           | 1
2     | 2           | 2           | 2
3     | 1           | 2           | 1
4     | 2           | 1           | 1
5     | 1           | 1           | 2
6     | 2           | 2           | 1
7     | 1           | 1           | 2
8     | 2           | 1           | 1
9     | 1           | 2           | 1
10    | 2           | 2           | 1

Now I want to join all tables with 4 and get this type of output

name    | count
{A,B}   | {5, 5}
{C,D}   | {5, 6}
{E,F}   | {7, 3}

I tried this

select array_agg(distinct(t1.name)), array_agg(distinct(temp.test))
from  
 (select t4.name1_id, (count(t4.name1_id)) "test" 
    from table4 t4 group by t4.name1_id
) temp
join table1 t1
on temp.name1_id = t1.id

I am trying to achieve this. Anybody can help me.

Answer 1

Calculate the counts for every table separately and union the results:

select 
    array_agg(name order by name) as name, 
    array_agg(count order by name) as count
from (
    select 1 as t, name, count(*)
    from table4
    join table1 t1 on t1.id = name1_id
    group by name
    union all
    select 2 as t, name, count(*)
    from table4
    join table2 t2 on t2.id = name2_id
    group by name
    union all
    select 3 as t, name, count(*)
    from table4
    join table3 t3 on t3.id = name3_id
    group by name
    ) s
group by t;

 name  | count 
-------+-------
 {A,B} | {5,5}
 {C,D} | {4,6}
 {E,F} | {7,3}
(3 rows)