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.

please tag the appropriate database system . You can't be using all the different database system at the same time — Squirrel
– Squirrel, Commented Aug 1, 2017 at 5:51
I tried this... select array_agg(distinct(rt.name)), array_agg(distinct(temp.test)) from (select f.resource_type_id, (count(f.resource_type_id)) "test" from planner.resource_entity f join planner.resource_type ft on f.resource_type_id = ft.id group by f.resource_type_id) temp join planner.resource_type rt on temp.resource_type_id = rt.id — Durgpal Singh
– Durgpal Singh, Commented Aug 1, 2017 at 5:55

klin · Accepted Answer · 2017-08-01 06:36:51Z

3

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)

answered Aug 1, 2017 at 6:36

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Durgpal Singh Over a year ago

If I used column data type is array in table 4 then.

Collectives™ on Stack Overflow

How to use join with aggregate function in postgresql?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related