Oracle – Inner Join Select with Group By

join;oracle

I have 2 tables (Oracle database):

ANEXA2A_TABEL1:

id  fk_id_anexa pet1    pet2    alt1    alt2
1   123         10      1       20      2
2   321         11      2       10      3

ANEXA2A_TABEL2_ROWS:

id  fk_id_anexa tip cant
1   123         PET 100
2   123         PET 10
3   123         ALT 200
4   123         ALT 20
5   321         PET 11
6   321         ALT 22

And I need to create a select (used after that in a view) to have a column like in that example:

id  fk_id_a pet1    pet2    pet3    alt1    alt2    alt3
1   123     10      1       110     20      2       220
2   321     11      2       11      10      3       22

I tried:

SELECT a2a.ID, a2a.FK_ID_ANEXA, a2a.PET1, a2a.PET2, a2a.ALT1, a2a.ALT2,
    (SELECT SUM(CANT) FROM ANEXA2A_TABEL2_ROWS where TIP = 'PET') AS PET3,
    (SELECT SUM(CANT) FROM ANEXA2A_TABEL2_ROWS where TIP = 'ALT') AS ALT3
FROM ANEXA2A_TABEL1 a2a
    INNER JOIN ANEXA2A_TABEL2_ROWS a2a2
        ON a2a2.FK_ID_ANEXA = a2a.FK_ID_ANEXA

but I get something like:

id  fk_id_a pet1    pet2    pet3    alt1    alt2    alt3
1   123     10      1       110     20      2       220
1   123     10      1       110     20      2       220
1   123     10      1       110     20      2       220
1   123     10      1       110     20      2       220
2   321     11      2       11      10      3       22
2   321     11      2       11      10      3       22

how can I get just that?

id  fk_id_a pet1    pet2    pet3    alt1    alt2    alt3
1   123     10      1       110     20      2       220
2   321     11      2       11      10      3       22

I guess I have to use a Group By – but I don't know how.
Thanks!

Best Answer

There are several ways to do this. Either remove the join and convert the subqueries to correlated:

SELECT a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2,
    (SELECT SUM(b.CANT) FROM ANEXA2A_TABEL2_ROWS b
     WHERE b.TIP = 'PET' AND b.FK_ID_ANEXA = a.FK_ID_ANEXA
    ) AS PET3,
    (SELECT SUM(b.CANT) FROM ANEXA2A_TABEL2_ROWS  b
     WHERE b.TIP = 'ALT' AND b.FK_ID_ANEXA = a.FK_ID_ANEXA
    ) AS ALT3
FROM ANEXA2A_TABEL1  a ;

or keep the join and use GROUP BY (I converted to LEFT join so you also see results for values that the second table has no related rows):

SELECT a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2,
    SUM(CASE WHEN b.TIP = 'PET' THEN b.CANT ELSE 0 END) AS PET3,
    SUM(CASE WHEN b.TIP = 'ALT' THEN b.CANT ELSE 0 END) AS ALT3
FROM ANEXA2A_TABEL1  a
    LEFT JOIN ANEXA2A_TABEL2_ROWS  b
        ON b.FK_ID_ANEXA = a.FK_ID_ANEXA 
GROUP BY 
    a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2 ;

There are several more options to do this (i.e. first group by table b in a derived table and the join to table a, or using PIVOT, or using the specific to Oracle function DECODE instead of CASE, ...)

Related Question