CODEWITHSUNDEEP
sqloracle-databaseoracle11gsum
Madmartigan
Madmartigan

Reputation: 453

Oracle sum previous values

I´m trying to do the the following: With this table:

DATE       VAR       
---------------
 2011      2.82          
 2012     -3.47        
 2013     -5.8        
 2014      13

I need to obtain this other table:

 DATE    VAR       ACUM    
---------------------------------------
 2011    2.82         
 2012   -3.47     -0.65      
 2013   -5.8      -6.45      
 2014    13        6.55

i.e. i nedd another column with the VAR value plus the sum of the all previous values.

for the 2014 row....13-5.8-3.47+2.82 = 6.55
for the 2013 row....-5.8-3.47+2.82 = -6.45

and so on.

Upvotes: 2

Views: 12999

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

Use Sum() Over() trick to find the running total

SELECT "DATE",
       VAR,
       Sum(VAR)OVER(ORDER BY "DATE") as ACUM
FROM   Yourtable

Or correlated subquery

SELECT "DATE",
       VAR,
       (SELECT Sum(VAR)
        FROM   Yourtable b
        WHERE  a."DATE" > b."DATE") ACUM
FROM   Yourtable a

Do not use DATE as column name, you would have to enclose it within double quotes all the time. And you also make it case sensitive by doing so. Bad idea.

Upvotes: 9

Related Questions