|
Home > Archive > Microsoft SQL Server forum > September 2005 > Getting Avg to really ignore null values
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Getting Avg to really ignore null values
|
|
| manning_news@hotmail.com 2005-09-02, 11:23 am |
| 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+N
ull)/8) would be ((5+5+5+4+5+5)/6)
after nulls ignored.
Thanks for any help or advice.
| |
| David Portas 2005-09-02, 11:23 am |
| 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 2005-09-02, 11:23 am |
| 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+N
ull)/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 2005-09-02, 11:23 am |
| < manning_news@hotmail
.com> wrote in message
news:1125674778.208211.43190@z14g2000cwz.googlegroups.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+N
ull)/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@hotmail.com 2005-09-02, 11:23 am |
| 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+systems
2+systems3+systems4+
systems5) from
tblEvaluations
All columns are tinyint
| |
| Robert Klemme 2005-09-02, 11:23 am |
| manning_news@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+systems
2+systems3+systems4+
systems5) from
> tblEvaluations
select
avg(ISNULL(systems1,
0)+ISNULL(systems2,0
)+ISNULL(systems3,0)
+ISNULL(system
s4,0)+ISNULL(systems
5,0)) from
tblEvaluations
robert
| |
| David Portas 2005-09-02, 11:23 am |
| Try:
SELECT
AVG(COALESCE(systems
1,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@hotmail.com 2005-09-02, 1:23 pm |
| Thanks, David & Robert. I believe that's what I needed.
| |
| manning_news@hotmail.com 2005-09-02, 1:23 pm |
| Thanks to everyone one that posted. David, I believe that's what I
needed.
|
|
|
|
|