Avg() not including 0"s

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?

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

Re: Avg() not including 0"s

On Wed, 30 Jan 2008 13:18:59 -0800 (PST), 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?

Hi Lee,

I was unable to reproduce this behaviour. Can you post some code (i.e. a
full repro script: CREATE TABLE statements, INSERT statements, and the
offending query) that I can run on my test server that does show this
behaviour on your machine?

I suspect something else is biting you, but I have to see a repro to
find out what it is.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Re: Avg() not including 0"s

Thanks for the quick responses. I know what was happening here but
unfortunately it brings me to another problem.

This was a problem with a group by:

select distinct [kw id], avg([bulk sd cr]) as [bulk SD] from data$
group by [kw id], [bulk sd cr]
order by [bulk sd] desc

Screwed up my averages. For it to be proper, it's

select distinct [kw id], avg([bulk sd cr]) as [bulk SD] from data$
group by [kw id]
order by [bulk sd] desc

However, the reason I had [bulk sd cr] in the group by in the first
place is because I have a case statement for sorting like:

order by case when @sortvar = 1 then [bulk sd cr]
when @sortvar = 2 then [kw id]
end desc

My case statement only works if I have all the fields in that case
statement also in the group by. This doesn't make sense to me - why
should the order by work fine when it's not a case statement, but
break when it is a case statement?




On Jan 30, 4:52 pm, Hugo Kornelis
wrote:
> On Wed, 30 Jan 2008 13:18:59 -0800 (PST), 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?
>
> Hi Lee,
>
> I was unable to reproduce this behaviour. Can you post some code (i.e. a
> full repro script: CREATE TABLE statements, INSERT statements, and the
> offending query) that I can run on my test server that does show this
> behaviour on your machine?
>
> I suspect something else is biting you, but I have to see a repro to
> find out what it is.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

Re: Avg() not including 0"s

lee.richmond wrote:

> This was a problem with a group by:
>
> select distinct [kw id], avg([bulk sd cr]) as [bulk SD] from data$
> group by [kw id], [bulk sd cr]
> order by [bulk sd] desc
>
> Screwed up my averages. For it to be proper, it's
>
> select distinct [kw id], avg([bulk sd cr]) as [bulk SD] from data$
> group by [kw id]
> order by [bulk sd] desc
>
> However, the reason I had [bulk sd cr] in the group by in the first
> place is because I have a case statement for sorting like:
>
> order by case when @sortvar = 1 then [bulk sd cr]
> when @sortvar = 2 then [kw id]
> end desc
>
> My case statement only works if I have all the fields in that case
> statement also in the group by. This doesn't make sense to me - why
> should the order by work fine when it's not a case statement, but
> break when it is a case statement?

Presumably the original statement looked like this:

select distinct [kw id], avg([bulk sd cr]) as [bulk SD] from data$
group by [kw id]
order by case when @sortvar = 1 then [bulk sd cr]
when @sortvar = 2 then [kw id]
end desc

which certainly won't work when @sortvar = 1, so SQL Server probably
decides to toss it out entirely.

Does this work, instead? Is it what you intended?

select distinct [kw id], avg([bulk sd cr]) as [bulk SD] from data$
group by [kw id]
order by case when @sortvar = 1 then avg([bulk sd cr])
when @sortvar = 2 then [kw id]
end desc