Re: How to write a "running sum" report via sql*plus ??
Date: Sat, 08 Apr 2000 20:43:19 GMT
Message-ID: <8co5l5$70i$1_at_nnrp1.deja.com>
Hello
I know not how to do this in SQL/Plus for there is no way to fetch different rows of a cursor individually. One workaround would be writing an anonymous PL/SQL-Block delivering the results with dbms_output:
set serveroutput on
1 declare
2 runsumm number := 0 ;
3 begin
4 for recEmp in (select * from scott.emp) loop
5 dbms_output.put_line (to_char(recEmp.empno) || ' ' ||
recEmp.ename || ' ' ||
6 to_char(recEmp.sal) || ' ' || to_char(recEmp.sal + runsumm)) ; 7 runsumm := runsumm + recEmp.sal ;8 end loop ;
9* end ;
gives
7839 King 5000 5000 7698 Blake 2850 7850 7782 Clark 2450 10300 7566 Jones 2975 13275 7654 Martin 1250 14525 7499 Allen 1600 16125 7844 Turner 1500 17625 7900 James 950 18575 7521 Ward 1250 19825 7902 Ford 3000 22825 7369 Smith 800 23625 7788 Scott 3000 26625 7876 Adams 1100 27725 7934 Miller 1300 29025
Of course, it is not SQL-Plus. And your must have access to the dbms_output-Package. And you had to format your result by yourself.
On the other hand, there is a way to produce running sums in an SQL-Statement: Make a self-join for the current row and all the rows before, and group by all the columns of the leading table:
select e1.empno, e1.ename, e1.sal, sum(e2.sal) summsal
from scott.emp e1, scott.emp e2
where e1.ename >= e2.ename -- must reflect order-by
group by e1.empno, e1.ename, e1.sal -- all selected cols of e1
order by ename
7876 Adams 1100 1100 7499 Allen 1600 2700 7698 Blake 2850 5550 7782 Clark 2450 8000 7902 Ford 3000 11000 7900 James 950 11950 7566 Jones 2975 14925 7839 King 5000 19925 7654 Martin 1250 21175 7934 Miller 1300 22475 7788 Scott 3000 25475 7369 Smith 800 26275 7844 Turner 1500 27775 7521 Ward 1250 29025
However, the query intermediately produces the cartesian product of the table (or at least something near one half of the cartesian product), thus beeing very sensitive to the size of your table:
SELECT STATEMENT Optimizer=CHOOSE
SORT (ORDER BY)
SORT (GROUP BY)
NESTED LOOPS
TABLE ACCESS (FULL) OF EMP
TABLE ACCESS (FULL) OF EMP
Any other suggestions ?
Pascal
In article <MPG.135964d8a531805b989680_at_news.gcn.net.tw>,
feib <agi_at_feib.com.tw> wrote:
> Hello,everyone
>
> Is it possible to generate a "running sum report" via sql*plus ??
> for example,
>
> product_id price
> ------------- -------
> item1 100
> item2 120
> item3 130
>
> How to write the script to generate the report like follows ?
>
> product_id price running_sum
> -------------- -------- -----------------
> item1 100 100
> item2 120 220 (item1+ item2)
> item3 130 350 (item1+ item2+
item3)
>
> ......
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Sat Apr 08 2000 - 22:43:19 CEST