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