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@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 - 06:52:31 CDT
