Re: AVG across columns

Geoff Muldoon wrote:
> Hi all,
>
> Oracle 10.2 on linux ...
>
> Is there any inbuilt way of calculating the average for values in separate
> columns of a table across a row (cf. the average of values in rows for a
> column) which allows for null values?
>
> 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.

And poorly doesn't even begin to describe it. <g>

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org