Re: Avg() not including 0's

"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
.

Relevant Pages

  • Re: Avg() not including 0s
    ... I have a sql query like this ... order by [mycolumn] desc ... But sql is returning a value as if my 0's were nulls and not factored ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (comp.databases.ms-sqlserver)
  • Re: NZ Function?
    ... SELECT COALESCE(MyColumn, 0) AS MyColumn ... SQL Server MVP ... "Joe Williams" wrote in message ... I put it in a SQL view and I get an unknown function ...
    (microsoft.public.sqlserver.programming)
  • default constraint
    ... is there any SQL that can be fired off against Access that will cause it to ... alter table mytable add constraint myconstraint default for mycolumn ... either over ADO or within Access. ...
    (microsoft.public.vb.general.discussion)
  • default constraint
    ... is there any SQL that can be fired off against Access that will cause it to ... alter table mytable add constraint myconstraint default for mycolumn ... either over ADO or within Access. ...
    (microsoft.public.sqlserver.programming)
  • default constraint
    ... is there any SQL that can be fired off against Access that will cause it to ... alter table mytable add constraint myconstraint default for mycolumn ... either over ADO or within Access. ...
    (microsoft.public.access.modulesdaovba)