Posted by Robert Klemme on 09/02/05 18:43

manning_news@hotmail.com wrote:
> Using SQL2000. According to Books Online, the avg aggregrate function
> ignores null values. ((3+3+3+3+Null)/5) predictably returns Null. Is
> there a function to ignore the Null entry, adjust the divisor, and
> return a value of 3? For example:((3+3+3+3)/4) after ignoring Null
> entry.
>
> If there's more than one null value, then adjust divisor accordingly.
> For example: ((5+5+5+4+Null+5+5+Null)/8) would be ((5+5+5+4+5+5)/6)
> after nulls ignored.
>
> Thanks for any help or advice.

Works for me:


create table t1 (
name varchar(20),
val int)

insert into t1 values ('f1', 1)
insert into t1 values ('f2', 2)
insert into t1 values ('f3', 3)

select * from t1

select avg(val) as [avg]
from t1

insert into t1 values ('f4', NULL)

select * from t1

select avg(val) as [avg]
from t1

drop table t1


Output


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)

name val
-------------------- -----------
f1 1
f2 2
f3 3

(3 row(s) affected)

avg
-----------
2

(1 row(s) affected)


(1 row(s) affected)

name val
-------------------- -----------
f1 1
f2 2
f3 3
f4 NULL

(4 row(s) affected)

avg
-----------
2

(1 row(s) affected)

Warnung: NULL-Wert wird durch eine Aggregat- oder eine andere
SET-Operation gelöscht.


What exactly is your problem?

Cheers

robert

• Next in forum: Re: Getting Avg to really ignore null values
• Prev in forum: Re: Getting Avg to really ignore null values
• Thread view: Re: Getting Avg to really ignore null values

[Reply to this message]