-
Add each row?
I have a table A which has the following data:
A
----
1
2
3
4
9
2
I need the following output thruogh SQL.
ACCUMULATIVE
-------------
1
3
6
10
19
21
It will hold the sum of the last row and add in it the next row.
I hope, i have convayed the question clearly.
Regards,
Nametullah kalair
Always Remember Power is Your.
-
is it possible to use a procedure for handling it ?
Thanks
Aiman Al-Jumoay
-
Analytic functions are the key to this ...
Code:
SQL> set feedback off
SQL> create table a (pos number, a number);
SQL> insert into a values (1,1);
SQL> insert into a values (2,2);
SQL> insert into a values (3,3);
SQL> insert into a values (4,4);
SQL> insert into a values (5,9);
SQL> insert into a values (6,2);
SQL>
SQL> select sum(a) Over (Order By pos range unbounded preceding)
2 from a
3 order by pos;
SUM(A)OVER(ORDERBYPOSRANGEUNBOUNDEDPRECEDING)
---------------------------------------------
1
3
6
10
19
21
I added a pos column to indicate the order in which you wanted the result set returned and the cumulative sum calculated.
-
Dear Developer,
the solution is a bit too long and stupid I would rather if your stick with the Position solution posted already , but anyway it was a try
--------------------------------------------------------------
CREATE OR REPLACE procedure calc_a
is
x number ;
out number ;
begin
select count( id ) into x from a ;
out := 0 ;
for i in 1..x loop
select sum( id ) into out from a
where dbms_rowid.rowid_row_number(rowid) + 1 between 1 and i;
dbms_output.PUT_LINE( out ) ;
end loop ;
end ;
Thanks
Aiman Al-Jumoay
-
Hi,
I think best way is :
select sum(id) over(order by rowid) ACCUMULATIVE
from a
ACCUMULATIVE
===========
1
4
6
10
18
20
29
-
-
bhavesh_09, Thanks my problem has been resolved. It was a smartest way.
Regards,
Nametullah kalair
Always Remember Power is Your.
-
Originally posted by nametullah
bhavesh_09, Thanks my problem has been resolved. It was a smartest way.
Regards,
Nametullah kalair
Relying on rowid for ordering is smart, eh? Interesting idea.
-
Relying on rowid for ordering is smart, eh? Interesting idea.
Yeh, i know what r u thinking slimdave, but problem is resolved. But i m also agree with u and ur example. Thanks for helping me.
Regards,
Nametullah kalair
Always Remember Power is Your.
-
Originally posted by nametullah
Yeh, i know what r u thinking slimdave, but problem is resolved. But i m also agree with u and ur example. Thanks for helping me.
Regards,
Nametullah kalair
Not a general solution though.
Slimdave's solution is the most general one.
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|