CODEWITHSUNDEEP
oracle-databasesumwindow-functionscumulative-sum
Waliul Islam
Waliul Islam

Reputation: 3

Get Successive Sum in ORACLE 11.2

I have a table which holds some (positive) numeric values in 2 columns, one for positive and one for negative values.

test_table 

POSITIVEVALUE | NEGATIVEVALUE
----------------------------------
100 
              | 50
70            |
              | 80
100           | 30

I need to get the successive sum of the row-total in each row. I've tried some things by LAG(), which results as below.

Query:

SELECT PositiveValue,
       NegativeValue, 
       RowTotal,
       NVL( (LAG(RowTotal, 1, NULL) OVER (ORDER BY ROWNUM)), 0) AS LagValue, 
       ( NVL( (LAG(RowTotal, 1, NULL) OVER (ORDER BY ROWNUM)), 0)
          + (NVL(RowTotal, 0))) AS SuccessiveSum
FROM(
     SELECT PositiveValue,
            NegativeValue, 
            NVL(PositiveValue, 0) - NVL(NegativeValue, 0) AS RowTotal
      FROM test_table);

Result:

POSITIVEVALUE   NEGATIVEVALUE   ROWTOTAL    LAGVALUE    SUCCESSIVESUM
100                             100         0           100
                50              -50         100         50
70                              70          -50         20
                80              -80         70          -10
100             30              70          -80         -10

How to get the result as the sum of row-total with the previous row-total like the following?

ROWTOTAL    SUCCESSIVESUM
100         100
-50         50
70          120
-80         40
70          110

Upvotes: 0

Views: 62

Answers (2)

Avrajit Roy
Avrajit Roy

Reputation: 3303

        Hello you can try this below code too as it gives decribed result 


        SELECT b.rowtotal,
  b.pos,
  b.neg,
  SUM(DECODE(NVL(b.neg,0),0,b.pos,'-'
  ||b.neg)) over(order by b.rn RANGE UNBOUNDED PRECEDING) cumm
FROM
  (SELECT A.POS-NVL(A.NEG,0) ROWTOTAL,
    A.POS,
    NVL(a.neg,0) neg,
    rownum rn
  FROM
    (SELECT 1000 AS POS,NULL AS NEG FROM dual
    UNION
    SELECT 2000 AS POS,600 AS NEG FROM dual
    UNION
    SELECT 500 AS POS,NULL AS NEG FROM dual
    UNION
    SELECT 1500 AS POS,100 AS NEG FROM dual
    UNION
    SELECT 900 AS POS,50 AS NEG FROM dual
    )A
  )b;

    ----------------------------OUTPUT -----------------------------------------

    ROWTOTAL    POS NEG CUMM
500 500 0   500
850 900 50  450
1000    1000    0   1450
1400    1500    100 1350
1400    2000    600 750

    -----------------------------------------------------------------------------

Upvotes: 0

Husqvik
Husqvik

Reputation: 5809

Just simple analytic sum?

WITH data (POSITIVEVALUE, NEGATIVEVALUE) AS (
    SELECT  100, NULL FROM DUAL UNION ALL
    SELECT NULL,   50 FROM DUAL UNION ALL
    SELECT   70, NULL FROM DUAL UNION ALL
    SELECT NULL,   80 FROM DUAL UNION ALL
    SELECT  100,   30 FROM DUAL
)
SELECT
    ROWTOTAL,
    SUM(ROWTOTAL) OVER (ORDER BY RN) SUCCESSIVESUM
FROM (
    SELECT
        ROWNUM RN,
        NVL(POSITIVEVALUE, 0) - NVL(NEGATIVEVALUE, 0) ROWTOTAL
    FROM
        data)

Upvotes: 1

Related Questions