As usual, no database version. Assuming you're on 8i+, take a look
at analyting functions for getting total for year and running total.
NVL(volume, 0) would also help you to get rid of NULLs. For example,
the following query will give almost the result you need (no rows
for missing years, and I don't have any idea of how to make them
appear in this query except that you insert a row for each missing year
with volume = 0 or NULL into source table before running this query):
select * from (
select region_name,
year,
sum(nvl(volume,0)) over (partition by region_name, year order by year) total_volume_this_year,
sum(nvl(volume,0)) over (partition by region_name order by year) accumulated_volume
from t
) group by region_name,
year,
total_volume_this_year,
accumulated_volume
If anyone knows how to add those fictious rows for missing years in this query, I'd like to see it.
:)
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"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 - 04:48:12 CDT
