|
|
Re: cumulative sum [message #638312 is a reply to message #638307] |
Tue, 09 June 2015 07:35 |
John Watson
Messages: 8929 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I would do this with a correlated sub-query, but I think that a better programmer would use the LAG analytic function.
|
|
|
Re: cumulative sum [message #638314 is a reply to message #638312] |
Tue, 09 June 2015 07:40 |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
No need for LAG, simple analytic sum will do:
with t as (select level l
from dual
connect by level <= 10)
select l, sum (l) over (order by l) cum
from t
TA I NEED to start readingh the question fully. Sorry John, I saw the question as simply a query not an update.
[Updated on: Tue, 09 June 2015 08:08] Report message to a moderator
|
|
|
|
|
Re: cumulative sum [message #638322 is a reply to message #638320] |
Tue, 09 June 2015 08:16 |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Yeah as Mr W above said, a correlated subquery would do the job:
create table t as select level l, 0 x
from dual
connect by level <= 10;
update t set x = (with sub as (select l, sum (l) over (order by l) cum
from t)
select cum
from sub
where sub.l = t.l) ;
|
|
|
Re: cumulative sum [message #638331 is a reply to message #638322] |
Tue, 09 June 2015 10:30 |
mnitu
Messages: 159 Registered: February 2008 Location: Reims
|
Senior Member |
|
|
I will prefer Merge if possible but I wonder why anyone should update the table to store the cumulative sum which will became obsolete when you will add or remove a row /into from your table. Probably you should use a view.
|
|
|
|
|