Re: Cumulative query

In article <nBtm5.15921$r4.8198_at_news.indigo.ie>,   "Conan" <conan@<no-spam>as-if.com> wrote:
> this should work
>
> select a.createdate, count(b.itemid)
> from your_table a, your_table b
> where b.createdate <= a.createdate
> group by a.createdate
> /
>
> HTH
>
> Conan
>
> ruudbax_at_my-deja.com wrote in message <8ndmfc$6db$1_at_nnrp1.deja.com>...
> >I'm trying to write a cumulative query on a table
> >that looks like this.
> >
> >createdate, itemid
> >
> >If the table would be filled with the following
> >data:
> >
> >01-01-2000 1
> >01-01-2000 2
> >01-01-2000 3
> >02-01-2000 4
> >02-01-2000 5
> >03-01-2000 6
> >04-01-2000 7
> >04-01-2000 8
> >
> >With the simple query by day (select count(*)
> >from table group by createdate) I would get the
> >number of items created on every date.
> >
> >I would however like to have a cumulative result,
> >so:
> >
> >Date Number of items
> >01-01-2000 3
> >02-01-2000 5 <3+2>
> >03-01-2000 6 <3+2+1>
> >04-01-2000 8 <3+2+1+2>
>

Here is a post from 08/20/1999 or earlier that talks about calculating a cumulative sum.

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/
Before you buy.