Re: AVG across columns

"Geoff Muldoon" <geoff.muldoon_at_trap.gmail.com> wrote in message news:MPG.21212be4e6552c17989995_at_news.readfreenews.net...
> DA Morgan 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.
> > >
> > > GM
> >
> > No.
>
> Bummer, as I suspected but I thought I'd ask, just in case ..
>
> > And poorly doesn't even begin to describe it. <g>
>
> Data is from a third-party <expletive> app. Looks like my only option
> within straight SQL is to "pseudo-normalize"/"de-case" the data using a
> series of UNION ALL joins.
>
> Unfortunately not very efficient, and the real data is more complex, about
> 30 cols, to be grouped into "sets" for averaging purposes.
>
> select row_id, col_group, avg(col_val) from (
> select row_id, 'group_1' col_group, v1 col_val
> from stupid_table
> union all
> select row_id, 'group_1' col_group, v2 col_val
> from stupid_table
> union all
> select row_id, 'group_1' col_group, v3 col_val
> from stupid_table
> union all
> select row_id, 'group_1' col_group, v4 col_val
> from stupid_table
> union all
> select row_id, 'group_2' col_group, w5 col_val
> from stupid_table
> union all
> select row_id, 'group_2' col_group, w6 col_val
> from stupid_table
> .......
> )
> group by row_id, col_group;
>
> :-(
>
> Any better alternatives appreciated.
>
> GM

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. Received on Sun Aug 05 2007 - 22:27:44 CDT