| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Cumulative query
If you are up to 8.1.6, the 'nicest' method is probably to use the 'analytic' mechanisms: Something like:
select
create_date,
ct,
sum(ct) OVER (
order by create_date
rows unbounded preceding
) cumulative_count
select
create_date,
count(item_id) ct
from
tabX
-- Jonathan Lewis Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk Mark D Powell wrote in message <8nes2o$j8l$1_at_nnrp1.deja.com>...Received on Wed Aug 16 2000 - 15:35:53 CDT
>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.
 |
 |