Re: Accumulated SUM in SQL

Hello David,

> I was trying to generate the report from table SALGRADE in SQL script only.
> The report layout look like one based column and one accumulated sum column:
> Sales Accumulted
> Sum
> 700 700
> 1201 1901
> 1201 3102
> 1401 4503
> 1401 5904
> 2001 7905
> 3001 10906
>
> The data source are from SALGRADE table in SCOTT/TIGER
> GRADE LOSAL
> ---------- ----------
> 1 700
> 2 1201
> 3 1401
> 4 2001
> 5 3001
> 6 1401
> 7 1201
>
> 7 rows selected.
>
> But the following script is generated with wrong result in row 3 and 5:
>
> SQL> select b.grade,sum(c.losal) from salgrade b,salgrade c
> 2 where b.grade >= c.grade
> 3 group by b.grade
> 4 /
>
> GRADE SUM(C.LOSAL)
> ---------- ------------
> 1 700
> 2 1901
> 3 3302
> 4 5303
> 5 8304
> 6 9705
> 7 10906
>
> 7 rows selected.

Your script gives you good results : you just need to sum 'losal' in the order by increasing grade.
first row = 700 (grade 1)
second row = 1901 (700+1201, grade 1 + grade 2) third row = 3302 (700+1201+1401, grade 1 + grade 2 + grade 3) ...

Maybe you want first order rows according losal and then apply sum. If you have Oracle 8i, you could try the following

select a.rownum_a, a.losal, sum(b.losal) from
(select rownum rownum_a, grade, losal
 from (select grade, losal from SALGRADE order by losal)) a, (select rownum rownum_b, grade, losal
 from (select grade, losal from SALGRADE order by losal)) b where a.rownum_a >= b.rownum_b
group by a.rownum_a, a.losal
order by sum(b.losal);

 ROWNUM_A LOSAL SUM(B.LOSAL)
--------- --------- ------------

        1       700          700
        2      1201         1901
        3      1201         3102
        4      1401         4503
        5      1401         5904
        6      2001         7905
        7      3001        10906
 

Have a nice day,

Erika Received on Thu Mar 08 2001 - 03:25:43 CST