group by question

[Ofina]

Sample table
Order field1 field2 field3 Cost
123 Y ABC BCD $2
123 Y ABC ABC $5

Say I have a select statement containing:
case field1
when 'Y' then field2
else field3
end as 'Supplier'

This will result in displaying 'ABC' in the Supplier field correctly.

However, there is a group by statement involved. It wants me to group by both field2 and field3 because they are both in the select statement. But, I would like to group by the result of my case statement. In the example above, I would like:
Order Supplier Cost
123 ABC $7

Does that make any sense?

 

[markros]

Yes. Kalman Toth wants to make your wish come true here

MSDN Thread about using column alias everywhere in the query

But since this wish unlikely to be granted, you need to use derived table or CROSS APPLY solution.



PluralSight Learning Library

 

[Ofina]

Actually I just tried putting the entire case statement in my group by and it seems to be fine.

Any reason I shouldn't do that?

 

[markros]

No, that's fine - CROSS APPLY is another technique I use to avoid repeating complex expressions.
In your case the expression is simple enough.

PluralSight Learning Library