Dec 6, 2010

Cumulative Sum in Oracle

In this post, I am sharing the code to calculate Cumulative sum in Oracle

Table Sample Data

Table Sample Data

Query to get Cumulative Sum of Amount on the basis of Type

SELECT NUM
       , DECODE(TYPE, 'DR', AMOUNT)
       , DECODE(TYPE, 'CR', AMOUNT)
       , SUM (DECODE(TYPE, 'CR', AMOUNT, -1 * AMOUNT))
         OVER (ORDER BY NUM ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
         AS BALANCE
FROM TBL
GROUP BY NUM, TYPE, AMOUNT
Output
Output

    Choose :
  • OR
  • To comment
No comments:
Write Comments