|
|
Posted by lee.richmond on 01/30/08 23:28
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...@perFact.REMOVETHIS.info.INVALID> 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
Navigation:
[Reply to this message]
|