I'm trying to manipulate data a little by using a case statement but can't seem to figure it out.
Stops
--------------------
1Stop
1-Stop
1 stop
1 Stop
1stop
1 Stop
2-Stop
Im trying to come up with:
1-Stop.... 6
2-Stop.... 1
What I've tried is:
select CASE when
Stops = '1Stop' OR
Stops = '1 Stop' OR
Stops = '1 stop' then '1-Stop'
ELSE Stops END, count(*)
from table group by Stops
Best Answer
The problem is that you can't use the alias
Stops
in theGROUP BY
. In your query when you are using theGROUP BY
it is using the individual values for each row. You'd need to use a subquery to get the result:See SQL Fiddle with Demo.
Or if you don't want to use a subquery, then you could repeat the
CASE
expression in theGROUP BY
:See SQL Fiddle with Demo