Re: Accumulated SUM in SQL

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 8 Mar 2001 09:02:18 -0000
Message-ID: <984043178.17395.0.nnrp-02.9e984b29@news.demon.co.uk>

It helps if you state Oracle versions.

Under 8.1.6 you can do this with the
analytic version of sum(), something like;

select

    sales,
    sum(sales) over(

        order by sales
        range unbounded preceding

    ) accumulated sales
from

    salgrade
;

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



David Wu wrote in message <986q7d$raa$1_at_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 Thu Mar 08 2001 - 03:02:18 CST