Add each row?

[nametullah]

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

[jomoueea]

is it possible to use a procedure for handling it ?

Thanks
Aiman Al-Jumoay

[slimdave]

Analytic functions are the key to this ...

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.

[jomoueea]

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

[bhavesh_09]

Hi,
I think best way is :
select sum(id) over(order by rowid) ACCUMULATIVE
from a

ACCUMULATIVE
===========
1
4
6
10
18
20
29

[slimdave]

"order by rowid"?

Yuk.

[nametullah]

bhavesh_09, Thanks my problem has been resolved. It was a smartest way.

Regards,

Nametullah kalair

[slimdave]

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.

[nametullah]

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

[simply_dba]

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.