6

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
9

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

  • Has anyone compared them by efficiency? – techkuz Feb 25 '19 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.