Avg() is not including 0's - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Avg() is not including 0's

Author

Topic

richmolj
Starting Member

3 Posts

Posted - 2008-01-30 : 16:06:38

Hi,

I have a sql query like this

select avg([mycolumn]) from data where date > '1/5/08' and date < '1/10/08'
group by [mycolumn]
order by [mycolumn] desc

If all values within that average are numbers, I'm fine. If it's a 0 (not a null, a 0) it doesn't get averaged in. For instance, values 0,1,2 should produce an average of 1.

(0+1+2)/3 = 1.

But sql is returning a value as if my 0's were nulls and not factored in:

(1+2)/2 = 1.5

Does anyone know why this is happening and how to fix it?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-30 : 16:25:17

Seems to work OK for me:


select
	MyAverage = avg(a.val),
	MyAverage2 = avg(a.val2)
from
	(
	select val = 0.0, val2 = 0 union all
	select val = 1.0, 1 union all
	select val = 2.0, 2
	) a

Results:
MyAverage                                MyAverage2  
---------------------------------------- ----------- 
1.000000                                 1

(1 row(s) affected)

CODO ERGO SUM

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-01-30 : 23:28:16

But If we need to include the NULL values too in the average, how do we go about it? Sum()/count(*)?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-30 : 23:35:29

[code]SELECT Avg1 = avg(a.val),
Avg2 = avg(ISNULL(a.val, 0))
FROM (
SELECT val = 0.0 UNION ALL
SELECT val = 1.0 UNION ALL
SELECT val = 2.0 UNION ALL
SELECT val = NULL
) a
/*
Avg1 Avg2
-------------- ------------
1.000000 .750000
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources