Re: Can rollup help in this situation?

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Fri, 30 Jul 2004 18:51:23 +0100
Message-ID: <tj2lg0lgvfsvojv7lactqgphhv52anu7o3@4ax.com>

On 30 Jul 2004 05:45:19 -0700, laredotornado_at_zipmail.com (D. Alvarado) wrote:

>Hi,
> I have a table that records number of new registrants in a day.
>Some records may look like
>
>DAY NUM_REGISTRANTS
>--- ---------------
>1 10
>2 5
>3 8
>
>I would like to create a SELECT statement such that I select the
>number of registrants per day, and the total number of registrants up
>to that point. Can I do that with SQL somehow? Ideally, a SELECT of
>the above data would yield
>
>DAY NUM_REGISTRANTS TOTAL_REGISTRANTS
>--- --------------- -----------------
>1 10 10
>2 5 15
>3 8 23
>
>Thanks for any advice, - Dave

SQL> create table day_registration_total   2 (

  3      day             number      not null,
  4      num_registrants number  not null,
  5      constraint day_registration_total_pk
  6          primary key (day)

  7 );

Table created.

SQL> insert into day_registration_total values (1, 10);

1 row created.

SQL> insert into day_registration_total values (2, 5);

1 row created.

SQL> insert into day_registration_total values (3, 8);

1 row created.

SQL> select day,

  2         num_registrants,
  3         sum(num_registrants) over (
  4            order by day
  5            rows unbounded preceding
  6         ) total_registrants

  7 from day_registration_total
  8 order by day;

       DAY NUM_REGISTRANTS TOTAL_REGISTRANTS

---------- --------------- -----------------
         1              10                10
         2               5                15
         3               8                23

--
Andy Hassall <andy_at_andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk         / http://www.andyhsoftware.co.uk/space

Received on Fri Jul 30 2004 - 12:51:23 CDT