Simple PL/SQL summation question

From: Roger <rwps2002_at_hotmail.com>
Date: 19 Jul 2002 01:01:51 -0700
Message-ID: <56b2ef9f.0207190001.60c0d49d@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:01:51 CDT