Re: SQL - Computing average for multiple columns

From: Stephen B <stephen.bell_at_cgi.ca>
Date: Thu, 27 Sep 2001 08:49:00 -0400
Message-ID: <gbFs7.20132$aP1.2836062@news20.bellglobal.com>

Hi Quincy,

How about something like this?

  1 select avg(a) from
  2 (select avg(A)a from numtablewithnull   3 union
  4 select avg(B) a from numtablewithnull   5 union
  6* select avg(C) a from numtablewithnull) SQL> /     AVG(A)

---

1901.14286

SQL> Statistically, you may introduce a variance by "averaging averages" but this should get you pretty close...the aggregate function will of course ignore the null values and average the others..

Hopefully this helps,

Steve

"Quincy Smith" <QuincyRS_at_hotmail.com> wrote in message news:770866f7.0109261250.a7e0b7_at_posting.google.com...
> OK, maybe I'm OVERTHINKING this, but is there a way to get the average
> of multiple columns, factoring in that some of the data may be null?
> As far as I can tell, I can't do an 'average(col1+col2+col3)'. I know
> (col1+col2+col3)/3 would work, but if col3 is null, then it really
> should be a divide by 2. I somehow got into using nvl and decode to
> assist, but it still doesn't account for a scenario where all three
> columns are null for a variable, since I still get a 'divide by zero'
> error for that.
>
> Thanks for any help.
Received on Thu Sep 27 2001 - 07:49:00 CDT