Re: Computing cumulative values with SQLplus (ANSWER)

In article <6qr79e$ed88$1_at_newssvr04-int.news.prodigy.com>, "BILL M COHEE" <COHEEB_at_prodigy.net> wrote:

>
> Alan D. Mills wrote in message <6qeteb$5r1$1_at_schbbs.mot.com>...
> SUM will onyl give you a total at break levels in SQL*PLus. I suspect
> you'll need your PL/SQL script and use it to populate a temporary table and
> drive your report from that.
>
> I've not managed to solve this any other way so if you get lucky, let me
> know.
>
> --
> Alan D. Mills
>
>
> Ed Prochak wrote in message <35CA7626.5B73_at_interfacefamily.com>...
> >Alan Long wrote:
> >> What I want is to add a column showing the cumulative quantity, like
> >>
> >
> >Look into your manual for the SUM() function.

I got two good responses:

George Jansen suggested:

  SQL> select t.part, t.qty, t.qty + sum(nvl(t2.qty, 0)) cum     2 from testing t, testing t2
    3 where t2.part(+) < t.part
    4 group by t.part, t.qty
    5 order by t.part;   

  PA QTY CUM

• ---------- ---------- aa 2 2 ab 1 3 ac 3 6

  SQL> Phill Weiss suggested lokking at: http://www.arrowsent.com/oratip/tip17.htm which produced:

SQL> select t1.part, t1.qty, sum(t2.qty)   2 from testing t1, testing t2
  3 where t2.part <= t1.part
  4 group by t1.part, t1.qty
  5 order by t1.part
  6

SQL> / PART QTY SUM(T2.QTY)
---- ---------- -----------

aa            2           2
ab            1           3
ac            3           6
 

Thanks to both for their help.

--
Alan Long
Materials Systems Group
Motorola, East Kilbride, Scotland
email tics28@"spam-off"email.sps.mot.com (remove the "spam-off") Received on Wed Aug 12 1998 - 05:35:56 CDT