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.

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;

CodePudding user response：

This is a typical gaps-and-islands problem. You can use LAG() in order to determine the exact partitions, and then SUM() analytic function such as

WITH ii AS
(
 SELECT i.*,
        ROW_NUMBER() OVER (ORDER BY id DESC) AS rn2,
        ROW_NUMBER() OVER (PARTITION BY floorNo ORDER BY id DESC) AS rn1
   FROM items i
)
SELECT id,mass,symbol, floorNo, 
       SUM(mass) OVER (PARTITION BY rn2-rn1 ORDER BY id DESC)-1 AS sumNext
  FROM ii 
 ORDER BY id

Demo

CodePudding user response：

You could use below solution which uses

• common table expression (cte) technique to put all consecutive rows with same FLOORNO value in the same group (new grp column).
• Then uses the analytic version of SUM function to sum all next MASS per grp column as required.

Items_RowsNumbered (id, mass, symbol, floorNo, rnb) as (
  select ID, MASS, SYMBOL, FLOORNO
    , row_number()over( 
          order by DECODE(symbol, 'P',1, 'D',2, 'L',3, 4 ) asc, ID )
  /*
    You need to add ID column (or any others columns that can identify each row uniquely)
    in the "order by" clause to make the result deterministic
    */
  from (Your source query)Items
)
, cte(id, mass, symbol, floorNo, rnb, grp) as (
  select id, mass, symbol, floorNo, rnb, 1 grp
  from Items_RowsNumbered
  where rnb = 1
  union all
  select t.id, t.mass, t.symbol, t.floorNo, t.rnb
    , case when t.floorNo = c.floorNo then c.grp else c.grp   1 end grp
  from Items_RowsNumbered t
  join cte c on (c.rnb   1 = t.rnb)
)
select 
    ID, MASS, SYMBOL, FLOORNO
  /*, RNB, GRP*/
  , nvl(
        sum(MASS)over(
          partition by grp
            order by rnb 
              ROWS BETWEEN 1 FOLLOWING and UNBOUNDED FOLLOWING)
       , 0 
      ) sumNext
from cte
;

demo on db<>fiddle