Re: Simple PL/SQL summation question

Why asking for trouble when the solution is easy and almost no data is involved?

Year table: Ice age until decline of mankind, < 100,000 entries, no realisticly it's less than 1,000...

Regions: 150 countries with 100 regions each, < 15,000 entries

-> algorithms OR data structures

Norbert

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> schrieb im Newsbeitrag news:ah8ukd$hh5$1_at_babylon.agtel.net...
> Well, this involves adding another table that holds year data to join with
> (or using rownum from some really big table for this purpose) - and my
> question was how to add rows for missing years using only the given table.
> I have a gut feeling that this is not possible, but I may very well be
wrong
> as already happened numerous times :)
> And by the way, sorry for typo in original post - I meant analytic
functions
> there, not "analyting".
>
> --
> 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.
>
>
> "Norbert Vossiek" <skipthis.norbert.vossiek_at_gmx.li> wrote in message
> news:ah8r4t$601$02$1_at_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 - 08:48:04 CDT