Here is a table of articles and I want to store sum of Mass Column from next rows in sumNext Column based on a condition.
If next row has same floor (in floorNo column) as current row, then add the mass of next rows until the floor is changed
E.g : Rows three has sumNext = 2. That is computed by adding the mass from row four and row five because both rows has same floor number as row three.
id | mass | symbol | floorNo | sumNext |
---|---|---|---|---|
2891176 | 1 | D | 1 | 0 |
2891177 | 1 | L | 8 | 0 |
2891178 | 1 | L | 1 | 2 |
2891179 | 1 | L | 1 | 1 |
2891180 | 1 | 1 | 0 | |
2891181 | 1 | 5 | 2 | |
2891182 | 1 | 5 | 1 | |
2891183 | 1 | 5 | 0 |
Here is the query, that is generating this table, I just want to add sumNext column with the right value inside.
WITH items AS (SELECT
SP.id,
SP.mass,
SP.symbol,
SP.floorNo
FROM articles SP
ORDER BY
DECODE(SP.symbol,
'P',1,
'D',2,
'L',3,
4 ) asc)
SELECT CLS.*
FROM items CLS;