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
<h...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> 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

.

Relevant Pages

  • Re: Generating one table with a terabyte of data
    ... Pretty much disabled the transaction log completely. ... We are using BULK INSERT ... for all those who don't know, SQL server prefers smaller BULK ... ORACLE seems to handle that 1 big file really well but with small files SQL ...
    (microsoft.public.sqlserver.dts)
  • Re: Recordset.AddNew and the recordset objects data retaining
    ... I am in an environment where bulk insert isn't an option--even though the ... underlying database is SQL server, I am using a third party OLE DB provider-- ... > insert(assuming you are using SQL server) or DTS. ...
    (microsoft.public.data.ado)
  • Re: Can Indexes effect Bulk Insert/Update Operations
    ... Columnist, SQL Server Professional ... becuase some operations need to be performed before insert/update the ... "Tom Moreau" wrote in message ... > unique/pk constraints before bulk loading the tables. ...
    (microsoft.public.sqlserver.programming)
  • Re: Timeout Expired
    ... So the timeout occurs during the bulk load operations? ... did you check the SQL Error Logs to see more clues about the timeout problem if there is any? ... locked for DML operations by SQL Server. ...
    (microsoft.public.sqlserver.server)
  • Re: Generating one table with a terabyte of data
    ... Run perfmon counters to a log and analyze them after a bulk load. ... > On fast bulk loads you should make sure that the database is not logging. ... >> I've locked SQL server to 1/3 physical memory usage. ...
    (microsoft.public.sqlserver.dts)