Re: A typical query

In article <7pk30n$37f$1_at_nnrp1.deja.com>,   venkatprakash_at_my-deja.com wrote:
> I have a typical problem to solve.
>
> IMPORTANT: I need to write a single SQL statement to solve the below
> problsm: Finding the cumulative value of n records. For eg
>
> sal
> ===
> 10
> 20
> 30
>
> The answer should be 10+(10+20)+(10+20+30)+...
> Can anybody give me a tip, please.
>
> Thanks
> V Prakash
>

I had saved the following for future review; I have yet to do the review but maybe this will be of help to you.

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") --
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Aug 20 1999 - 12:32:19 CDT