Getting Avg to really ignore null values

[manning_news@hotmail.com]

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.

[David Portas]

Re: Getting Avg to really ignore null values

The AVG function really does ignore NULL values. It behaves exactly the
way you say you want:

CREATE TABLE T (x INTEGER NULL /* ... */) ;

INSERT INTO T VALUES (3) ;
INSERT INTO T VALUES (3) ;
INSERT INTO T VALUES (3) ;
INSERT INTO T VALUES (3) ;
INSERT INTO T VALUES (NULL) ;

SELECT AVG(x) FROM T ;

Result:

-----------
3

(1 row(s) affected)

I don't understand what your example is supposed to illustrate because
you've only used literals. Are those values supposed to represent
scalar variables? In which case you could do something such as this:

SELECT
(COALESCE(@v1,0 )
+COALESCE(@v2,0 )
+COALESCE(@v3,0 )
+COALESCE(@v4,0 )
+COALESCE(@v5,0 ))
/(CASE WHEN @v1 IS NULL THEN 0 ELSE 1 END+
CASE WHEN @v2 IS NULL THEN 0 ELSE 1 END+
CASE WHEN @v3 IS NULL THEN 0 ELSE 1 END+
CASE WHEN @v4 IS NULL THEN 0 ELSE 1 END+
CASE WHEN @v5 IS NULL THEN 0 ELSE 1 END)

--
David Portas
SQL Server MVP
--

[Robert Klemme]

Re: Getting Avg to really ignore null values

manning_news@ho tmail.com wrote:[color=blue]
> 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.[/color]

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

[Brian Cryer]

Re: Getting Avg to really ignore null values

<manning_news@h otmail.com> wrote in message
news:1125674778 .208211.43190@z 14g2000cwz.goog legroups.com...[color=blue]
> 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.[/color]

Why not simply change your "where" clause so you aren't picking up null
values?

Brian.

--

https://www.cryer.co.uk/brian

[manning_news@hotmail.com]

Re: Getting Avg to really ignore null values

OK, maybe I have my avg function syntax wrong. Given the following set
of values I get a Null result:

<column names>
systems1 systems2 systems3 systems4 systems5
2 2 2 NULL NULL
3 4 5 5 NULL
4 1 2 4 NULL

select avg(systems1+sy stems2+systems3 +systems4+syste ms5) from
tblEvaluations

All columns are tinyint

[Robert Klemme]

Re: Getting Avg to really ignore null values

manning_news@ho tmail.com wrote:[color=blue]
> OK, maybe I have my avg function syntax wrong. Given the following
> set of values I get a Null result:
>
> <column names>
> systems1 systems2 systems3 systems4 systems5
> 2 2 2 NULL NULL
> 3 4 5 5 NULL
> 4 1 2 4 NULL
>
> select avg(systems1+sy stems2+systems3 +systems4+syste ms5) from
> tblEvaluations[/color]

select
avg(ISNULL(syst ems1,0)+ISNULL( systems2,0)+ISN ULL(systems3,0) +ISNULL(system
s4,0)+ISNULL(sy stems5,0)) from
tblEvaluations

robert

[David Portas]

Re: Getting Avg to really ignore null values

Try:

SELECT
AVG(COALESCE(sy stems1,0)
+COALESCE(syste ms2,0)
+COALESCE(syste ms3,0)
+COALESCE(syste ms4,0)
+COALESCE(syste ms5,0))
FROM tblEvaluations

Note however, that this is the Average of the row totals, not of the
individual values.

--
David Portas
SQL Server MVP
--

[manning_news@hotmail.com]

Re: Getting Avg to really ignore null values

Thanks, David & Robert. I believe that's what I needed.

[manning_news@hotmail.com]

Re: Getting Avg to really ignore null values

Thanks to everyone one that posted. David, I believe that's what I
needed.