Getting Avg to really ignore null values

[manning_news]

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]

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]

ma**********@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

[Brian Cryer]

<ma**********@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.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.

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

Brian.

--
www.cryer.co.uk/brian

[manning_news]

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+systems2+systems3+systems4+systems5) from
tblEvaluations

All columns are tinyint

[Robert Klemme]

ma**********@hotmail.com wrote:

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+systems2+systems3+systems4+systems5) from
tblEvaluations

select
avg(ISNULL(systems1,0)+ISNULL(systems2,0)+ISNULL(s ystems3,0)+ISNULL(system
s4,0)+ISNULL(systems5,0)) from
tblEvaluations

robert

[David Portas]

Try:

SELECT
AVG(COALESCE(systems1,0)
+COALESCE(systems2,0)
+COALESCE(systems3,0)
+COALESCE(systems4,0)
+COALESCE(systems5,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]

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

[manning_news]

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