Grouping on a case statement
-
Hey all,
is it not possible to group on a case statement?
Take the following Im working on, a simply table with Orders. I want to produce groups based on size of orders.
eg:
SELECT StoreID, CASE WHEN OrdersAmount > 10000 THEN 'Big Order' ELSE 'Small Order' as Category,
COUNT(*) as NumberOfOrders
FROM OrdersTable
GROUP BY StoreID, Category
I cant get this query to execute, complains that category is unrecognised. I even tried repeating the case statement in the group section instead of the column name Category, but that failed.
Any ideas?
-
What stops you from answering your own question?
Why not just try?
Believe me, there is no bomb inside, it will not blow if you cut the wrong wire.
And don't forget about syntax.
SELECT StoreID, CASE WHEN OrdersAmount > 10000 THEN 'Big Order' ELSE 'Small Order' END as Category,
COUNT(*) as NumberOfOrders
FROM OrdersTable
GROUP BY StoreID, CASE WHEN OrdersAmount > 10000 THEN 'Big Order' ELSE 'Small Order' END
_____________
Code for TallyGenerator -
I think the case statement in the group by clause will probably fail you but otherwise I think that should works as:
SELECT StoreID, (CASE WHEN OrdersAmount > 10000 THEN 'Big Order' ELSE 'Small Order' end) as Category,
COUNT(*) as NumberOfOrders
FROM OrdersTable
GROUP BY StoreID
Good Hunting!
-
Don't think. It's really bad habit.
Let horses think, their heads are much bigger.
Better try you suggessions.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply