Printable View
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
is it possible to use a procedure for handling it ?
Thanks
Aiman Al-Jumoay
Analytic functions are the key to this ...
I added a pos column to indicate the order in which you wanted the result set returned and the cumulative sum calculated.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
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
"order by rowid"?
Yuk.
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.Quote:
Originally posted by nametullah
bhavesh_09, Thanks my problem has been resolved. It was a smartest way.
Regards,
Nametullah kalair
Quote:
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
Not a general solution though.Quote:
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
Slimdave's solution is the most general one.
ROWID logic is stupid idea.
You will get wrong result set over period of time.
See below:
TamilPHP Code:
Logic based on the ROWID will NOT work always.
See the example below:
SQL> select * from a ;
B
----------
1
2
4
9
18
4
6 rows selected.
SQL> select ( sum(b) over(order by rowid)) accu from a ;
ACCU
----------
1
3
7
16
34
38
6 rows selected.
SQL> delete a where b in (1,2) ;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from a ;
B
----------
4
9
18
4
SQL> insert into a values (3) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from a ;
B
----------
4
9
18
4
3 --> new row
SQL> insert into a values(1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from a ;
B
----------
4
9
18
4
3
1 --- new row
6 rows selected.
SQL> insert into a values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from a ;
B
----------
2 ----> This new row has the lowest rowid
4
9
18
4
3
1
7 rows selected.
You will get completely WRONG result here.
SQL> select ( sum(b) over(order by rowid)) accu from a ;
ACCU
----------
2
6
15
33
37
40
41
7 rows selected.