Re: SQL - Computing average for multiple columns

instead of denominator '3' use this:
decode( (decode(col1,null,0,1)+decode(col2,null,0,1)+decode(col3,null,0,1)), 0,777,(decode(col1,null,0,1)+decode(col2,null,0,1)+decode(col3,null,0,1))) in case of col1=col2=col3=NULL, denominator will be 777 (or change on what u need, i guess '1');
i think in orger to count average u should divide by 3 in any case, but i don't know what u count :-), it's up to u. I hope i helped.

Alex.

"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 Wed Sep 26 2001 - 17:07:34 CDT