Re: Simple PL/SQL summation question

From: Norbert Vossiek <skipthis.norbert.vossiek_at_gmx.li>
Date: Fri, 19 Jul 2002 12:48:45 +0200
Message-ID: <ah8r4t$601$02$1@news.t-online.com>

How about this:

create table y (
 y NUMBER(4)
);

create table r (
 r VARCHAR2(10)
);

create table d (
 r VARCHAR2(10),
 y NUMBER(4),
 d NUMBER
);

insert into y values(2001);
insert into y values(2002);
insert into y values(2003);
insert into r values('USA');

insert into r values ('UK');

insert into d values('USA', 2001, 4711);
insert into d values('UK', 2001, 4712);
insert into d values('UK', 2003, 4713);

commit;

• first we need the cross product... create view r_cross_y as select r.r, y.y from r, y
• then all the possible values, NULLs mapped to 0 create view r_y_amount as select a.r, a.y, NVL(d.d, 0) amount from r_cross_y a, d where a.r = d.r (+) and a.y = d.y (+)
• then partition properly select r region, y year, sum(amount) over (partition by r, y order by y) total_volume_this_year, sum(amount) over (partition by r order by y) accumulated_volume from r_y_amount;

Norbert

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> schrieb im Newsbeitrag news:ah8nbm$f1q$1_at_babylon.agtel.net...
> 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_at_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 - 05:48:45 CDT