Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> AVG across columns

AVG across columns

From: Geoff Muldoon <geoff.muldoon_at_trap.gmail.com>
Date: Mon, 6 Aug 2007 10:51:59 +1000
Message-ID: <MPG.21211bb6cfa0bc24989994@news.readfreenews.net>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US