Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Can I achieve this in Oracle 8i?

Re: Can I achieve this in Oracle 8i?

From: stinky <stankonia_at_stunkitystunk.org>
Date: Thu, 02 May 2002 22:47:55 -0400
Message-ID: <3CD1FA5B.5080201@stunkitystunk.org>


Lucas Wells wrote:

>Hi All,
>
>Curious to know if I can achieve the following via PL/SQL in Oracle
>8i?
>
>My input table looks something like this:
>
>Date, ID
>01/01/2002, 12345
>01/01/2002, 98765
>01/02/2002, 12345
>01/03/2002, 12345
>01/03/2002, 98765
>01/03/2002, 80976
>01/04/2002, 12345
>
>The output recordset I am looking for would look like this:
>
>Date, CumulativeCount
>01/01/2002, 2
>01/02/2002, 3
>01/03/2002, 6
>01/04/2002, 7
>
>In essence, I want to count the IDs that appear on each dat AND
>cumulatively aggregate them for each subsequent date; so, the 2
>records from 01/01/2002 get added to the 1 record from 01/02/2002 to
>give a result of 3 for 01/02/2002. Similarly, the 3 now in 01/02/2002
>get added to the 3 from 01/03/2002 to five a result of 6 for
>01/03/2002 and so on.
>

In other words, you want a cumulative aggregate....just like you said in the first sentence, right? :)

>Any help would be greatly appreciated!
>

Try this:

select date, sum(count(id))
over (order by date

      range unbounded preceding) id
from (whatever your inut table is named) group by date

>
>
>Regards,
>
>LW
>
Received on Thu May 02 2002 - 21:47:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US