Cumulative SUMS in Oracle SQL


Before the advent of analytics most people would probably have tackled the problem below using some sort of precedural code e.g PL/SQL or Pro*C. However this is now quite easy to do with analytics.Lets say we have a table with the following information

ID Amount
​== ======
1 100
1 350
1 290
2 210
2 99
2 109
3 300

We would like to get out the folowing data

ID Amount CumSum
== ====== =======
1 100 100
1 350 450
1 290 740
2 210 210
2 99 309
2 109 418
3 300 300

​The following query will do this for us (note the order by has to be unique in what you are trying to sum up, in this case each individual record which is why we include rowid)

select id,amount, sum(amount) over (partition by id order by id,rowid range unbounded preceding) yy
from t1

ID AMOUNT YY
---- ---------- ----------
1 100 100
1 350 450
1 290 740
2 210 210
​2 99 309
2 109 418
​3 300 300


​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama