Re: AVG across columns

Tim B says...

> > > > Given this data in table x:
> > > > row_id v1 v2 v3 v4
> > > > A 5 10 null 9
> > > > B null 5 5 null
> > > > C 6 8 7 7
> > > >
> > > >
> > > > I'd like a query result that looks like:
> > > > row_id row_avg
> > > > A 8 -- (5+10+9)/3
> > > > B 5 -- (5+5)/2
> > > > C 7 -- (6+8+7+7)/4
> > > >
> > > > The simple math of (sum(v1 + v2 + v3 + v4) / 4) won't work due to null
> > > > addition, and nvl to sidestep this gives an incorrect denominator
> count.
> > > >
> > > > Solution needs to be SQL not PL/SQL.
> > > >
> > > > Yes, I know the data is poorly normalized.

> how about something like this, using nvl and nvl2?
> (not tested):
> select
> row_id,
> nvl(v1, 0) valv1,
> nvl2(v1, 1, 0) countv1,
> nvl(v2, 0) valv2,
> nvl2(v2, 1, 0) countv2 ,
> (valv1 + valv2)/(countv1 + countv2) ... etc.

Tested using small sample and yes, it works. Totally overlooked NVL2.

Thanks heaps!

Now trying with real data ...

GM Received on Sun Aug 05 2007 - 23:09:49 CDT