With a query like the following:
x
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)
Advertisement
Answer
You can use window functions:
select . . .,
sum(qty) over (partition by code1, code2, code3) as my_sum
from . . .