Posted by Ed Murphy on 01/31/08 04:21

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

[Back to original message]