Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Simple PL/SQL summation question
SELECT NVL(<col>,retval) ..... will help you in some of this
"Roger" <rwps2002_at_hotmail.com> wrote in message
news:56b2ef9f.0207190001.60c0d49d_at_posting.google.com...
> Hi,
>
> I have a table containing region_name, year and volume.
> No unique key, i.e, multiple volume numbers for one year and region is
> possible. Also, NULL value for volume is possible.
>
> Example:
> USA 1993 100
> USA 1993 50
> USA 1993 140
> USA 1993 (NULL)
> USA 1993 130
> USA 1994 120
> USA 1994 100
> USA 1997 90
> USA 1998 80
> .....
>
>
> I want to summarise this in an aggregagate table, with region_name,
> year, total_volume_this_year and accumulated_volume.
>
> Example aggregate table:
> USA 1993 420 420
> USA 1994 220 640
> USA 1995 0 640
> USA 1996 0 640
> USA 1997 90 730
> USA 1998 80 810
>
>
> How do I perform the summations?
> I've tried using cursors with loops, but it seems that NULL values
> destroy the content of my variable (accumulated values are OK until a
> NULL value is found).
> Also, some years don't appear in the source table, but I would like
> them in the aggregate table (keeping the same accumulated value as the
> previous year). How can this be done?
>
> Any hints are much appreciated!
> Thanks
>
> Roger
Received on Fri Jul 19 2002 - 03:42:08 CDT