| cumulative addition [message #21439] |
Fri, 02 August 2002 21:57  |
jeya
Messages: 18
Registered: January 2001
|
Junior Member |
|
|
|
How to display the cumulative addtion of a field (say salary) of a table ?
|
|
|
|
| Re: cumulative addition [message #21442 is a reply to message #21439] |
Sat, 03 August 2002 06:11  |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
You can try this out with a function, passing in the rownum that is being processed and summing the desired column uptil the passed in rownum (exclusive) and returning to the SELECT statement at SQL*PLUS.
It would look like:
SQL> ed
Wrote file afiedt.buf
1 create or replace function accumulator (p_num in integer)
2 return number
3 is
4 cumulative number;
5 begin
6 select sum(salary) into cumulative from myp_employee where rownum < p_num;
7 return (cumulative);
8* end;
SQL> /
Function created.
SQL> select salary, accumulator(rownum) from myp_employee;
SALARY ACCUMULATOR(ROWNUM)
---------- -------------------
3240
4300 3240
3540 7540
1320 11080
3240 12400
3200 15640
3400 18840
7 rows selected.
You can also try with little modification in the summing query making the current row inclusive (like, WHERE ROWNUM <= P_NUM in the above query), but be advised that the ORACLE does not promise you to process always right when the ROWNUM comparision includes an = (equality) operator.
There could be a better solution writing within a SELECT statement itself, as a self-query and returning it to the main SELECT onto the SQL*PLUS. Let me check it out and get back to you. Would also like to know if there is any better solution from our friends at this forum could offer one.
Good luck :)
|
|
|
|
|
|
| Re: cumulative addition [message #21573 is a reply to message #21439] |
Wed, 14 August 2002 09:41 |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
You could also try with SUM() with OVER clause. It would just look like,
SELECT ENO, ENAME, SALARY, SUM (SALARY) OVER (ORDER BY ROWNUM) TOTALSALARY FROM EMPLOYEE;
It lists the employees with an additional column of TOTALSALARY cumulative.
Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
