Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: AVG across columns
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 Received on Sun Aug 05 2007 - 21:00:58 CDT