CODEWITHSUNDEEP
postgresqlaggregate-functions
abe678
abe678

Reputation: 99

PostgreSQL multiple aggregate arrays

I have four tables

TABLE_A
----------------
ID AName1 AName2
1  A11    A12
2  A21    A22
3  A31    A32

TABLE_B
------------
AID ID BName
1   B11 Ba11
1   B12 Ba12
1   B13 Ba13
1   B14 Ba14
2   B21 Ba21
2   B22 Ba22
3   B31 Ba31

TABLE_C
----------------
AID BID ID CName
1   B11 C11 Ca11
1   B11 C12 Ca12
1   B11 C13 Ca13
1   B12 C14 Ca14
1   B13 C15 Ca15
1   B13 C16 Ca16
1   B14 C17 Ca17
1   B14 C18 Ca18
1   B14 C19 Ca19
2   B21 C20 Ca20
2   B21 C21 Ca21
2   B22 C22 Ca22
2   B22 C23 Ca23
2   B22 C24 Ca24
2   B22 C25 Ca25

TABLE_D
----------------
AID BID ID DName
1   B11 D1  Da1
1   B11 D2  Da2
1   B11 D3  Da3
1   B12 D4  Da4
1   B12 D5  Da5
1   B13 D6  Da6
1   B13 D7  Da7
1   B13 D8  Da8
1   B13 D9  Da9
1   B14 D10 Da10
2   B21 D11 Da11
2   B21 D12 Da12
2   B21 D13 Da13
2   B22 D14 Da14
2   B22 D15 Da15

Given a single pair of AName1 and AName2, I would like to get unique values of BName and the corresponding arrays of CName and DName.

For example, given AName1 = A21 and AName2 = A22, I would like to get the following:

BName CName                    DName
B21   {Ca20, Ca21}             {Da11, Da12, Da13}
B22   {Ca22, Ca23, Ca24, Ca25} {Da14, Da15}

I'm currently using the following query:

SELECT
b.BName,
array_agg(c.CName),
array_agg(d.DName)
FROM
TABLE_B
INNER JOIN
TABLE_A on b.AID=TABLE_A.ID
LEFT JOIN
TABLE_C c on c.BID = b.ID 
LEFT JOIN
TABLE_D d on d.BID=b.ID
WHERE
TABLE_A.AName1 like 'A21' AND
TABLE_A.AName2 like 'A22'
GROUP BY
b.ID

This is repeating values as if it is also grouping on C and D:

BName CName                                DName
B21   {Ca20, Ca20, Ca20, Ca21, Ca21, Ca21} {Da11, Da12, Da13, Da11, Da12, Da13}

Does anyone know how I might be able to correct this issue? I'm using PostgreSQL version 9.2.

Upvotes: 1

Views: 173

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125504

SQL Fiddle

select
    b.bname,
    array_agg(distinct c.cname),
    array_agg(distinct d.dname)
from
    table_b b
    inner join
    table_a a on b.aid = a.id
    left join
    table_c c on c.bid = b.id
    left join
    table_d d on d.bid = b.id
where
    a.aname1 = 'A21' and
    a.aname2 = 'A22'
group by b.bname

Upvotes: 2

Related Questions