Home » SQL & PL/SQL » SQL & PL/SQL » cumulative sum (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production)
cumulative sum [message #638307] Tue, 09 June 2015 07:29 Go to next message
naga.oracle
Messages: 6
Registered: August 2014
Location: india
Junior Member

Hi all,

i have a table like this

create table t(colA number(10),colB number(10));

insert into t(colA)values(1);
insert into t(colA)values(2);
insert into t(colA)values(3);
insert into t(colA)values(4);
insert into t(colA)values(5);

select * from t;
colA colB
----- -----
1
2
3
4
5

i want out put like this:
o/p;
----
colA colB
---- ----
1 1
2 3
3 6
4 10
5 15

i need to update the values in colB with cumulative sum of colA.

Thanks in advance,
naga
Re: cumulative sum [message #638310 is a reply to message #638307] Tue, 09 June 2015 07:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Re: cumulative sum [message #638312 is a reply to message #638307] Tue, 09 June 2015 07:35 Go to previous messageGo to next message
John Watson
Messages: 8919
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 Go to previous messageGo to next message
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 #638319 is a reply to message #638314] Tue, 09 June 2015 07:51 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
@naga. Do not pm me with additional questions on this. If you require further assistance, simply post on here.
Re: cumulative sum [message #638320 is a reply to message #638319] Tue, 09 June 2015 07:59 Go to previous messageGo to next message
naga.oracle
Messages: 6
Registered: August 2014
Location: india
Junior Member

ok,

colA data is entered already and colB data is nulll
so i need to update colB data with cumulative values of colA.
please help me as i am new to this.

i tried with below query.
update t set colB=(select sum(colA) over(order by colA) from t);

ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

Re: cumulative sum [message #638322 is a reply to message #638320] Tue, 09 June 2015 08:16 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: cumulative sum [message #638332 is a reply to message #638331] Tue, 09 June 2015 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Given the example I bet this is just an exercise.

Re: cumulative sum [message #638336 is a reply to message #638332] Tue, 09 June 2015 11:53 Go to previous message
naga.oracle
Messages: 6
Registered: August 2014
Location: india
Junior Member

Yes,

thank you pablolee.
Previous Topic: Fast query paging for large table
Next Topic: SQL query help
Goto Forum:
  


Current Time: Tue Mar 19 03:47:17 CDT 2024