Posted by Gert-Jan Strik on 01/30/08 21:49

"lee.richmond" wrote:
>
> 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?

Lee,

a simple test does not replicate your problem. The simple script below
returns an average of 1.0 on SQL Server 7.0, 2000 and 2005.

create table #t(v decimal(3,2))
insert into #t values (0)
insert into #t values (1)
insert into #t values (2)
select avg(v) from #t
drop table #t

If the problem persists, then please post a repro script and the version
of SQL Server you are using.

--
Gert-Jan

[Back to original message]