How do I select data with a case statement and group by?

Question

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

Answer 1

The problem is that you can't use the alias Stops in the GROUP BY. In your query when you are using the GROUP BY it is using the individual values for each row. You'd need to use a subquery to get the result:

select stops, count(*) Total
from  
(
  select 
    CASE 
      when Stops in ('1Stop', '1 Stop', '1 stop') then '1-Stop' 
      ELSE Stops
    END as Stops
  from yourtable
) d
group by stops;

See SQL Fiddle with Demo.

Or if you don't want to use a subquery, then you could repeat the CASE expression in the GROUP BY:

select 
  CASE 
    when Stops in ('1Stop', '1 Stop', '1 stop') then '1-Stop' 
    ELSE Stops
  END as Stops,
  count(*)  as Total
from yourtable
group by 
  CASE 
    when Stops in ('1Stop', '1 Stop', '1 stop') then '1-Stop' 
    ELSE Stops
  END 

See SQL Fiddle with Demo

Answer 2

you can also try select CASE when Stops in ('1Stop', '1 Stop', '1 stop') then '1-Stop' ELSE Stops END as Stops, count(*) as Total from yourtable group by 1