Accumulated SUM in SQL

From: David Wu <david1027_at_hotmail.com>
Date: Thu, 8 Mar 2001 10:26:40 +0800
Message-ID: <986q7d$raa$1@clematis.singnet.com.sg>

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.

Is there a way to re-write the SQL to get correct result?

Thanks

David Wu Received on Wed Mar 07 2001 - 20:26:40 CST