Sum where values are equal between rows in certain columns in Oracle

With a query like the following:

SELECT TBL.ID, TBL.CODE1, TBL.CODE2, TBL.CODE3, TBL.CODE4, TBL.QTY
FROM DB.TBL TBL
GROUP BY TBL.ID, TBL.CODE1, TBL.CODE2, TBL.CODE3, TBL.CODE4, TBL.QTY

That returns data like the following:

    ID  |   CODE1   |   CODE2   |   CODE3   |   CODE4   |   QTY
--------+-----------+-----------+-----------+-----------+----------
    1   |   H1      |   G2      |   R3      |   MM      |   5
    2   |   F1      |   E2      |   J3      |   SS      |   2
    3   |   H1      |   G2      |   R3      |   LL      |   8
    4   |   C1      |   I2      |   O3      |   PP      |   6
    5   |   F1      |   E2      |   J3      |   EE      |   1

How can I add a column with the sum of the column QTY where the columns CODE1, CODE2 and CODE3 are the same between rows?

    ID  |   ... |   QTY |   SUM
--------+-------+-------+----------
    1   |   ... |   5   |   13
    2   |   ... |   2   |   3
    3   |   ... |   8   |   13
    4   |   ... |   6   |   6
    5   |   ... |   1   |   3

Something like the following:

SUM(CASE WHEN TBL.CODE1=TBL.CODE1 AND TBL.CODE2=TBL.CODE2 AND TBL.CODE3=TBL.CODE3 THEN TBL.QTY ELSE 0 END)

Answer

You can use window functions:

select . . .,
       sum(qty) over (partition by code1, code2, code3) as my_sum
from . . .

Advertisement

Answer