Filtering previous month data with case statement

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

May I suggest doing it in a couple of passes:

SQL> select * from records;

SUBJ_NO
--------------------------------------------------------------------------------
CBS405/505 Fourth Week(Wtr Qtr)
BR 404-504-604 Fifth Week(Fall Qtr)
AP490-QB-09 Weekly Sessions(Spr Qtr)
APR501 Fourth Week(Wtr Qtr)
MP 300 Fourth Week(Wtr Qtr)
ACX/501 Fifth Week(Sum Qtr)
ACX 400 Ninth Week(Mas Term)

7 rows selected.

SQL> l
  1  with strip as(
  2  select translate(subj_no, '/-', '  ') subj_no
  3    from records
  4  )
  5  , reg as (
  6  select regexp_substr(subj_no,'^[A-Z]{2,3}') col_2
  7   , trim(regexp_substr(subj_no,'[0-9 ]+')) col_3
  8    from strip
  9  )
 10  select col_2||col_3 col_1
 11   , col_2
 12   , col_3
 13*   from reg
SQL> /

COL_1                COL_2      COL_3
-------------------- ---------- --------------------
CBS405 505           CBS        405 505
BR404 504 604        BR         404 504 604
AP490                AP         490
APR501               APR        501
MP300                MP         300
ACX501               ACX        501
ACX400               ACX        400

7 rows selected.

Strip replaces the slashes and dashes with spaces so they don't get in the way later.

Reg rips off your regexp and adds another one that finds the first group of digits and spaces, trimmed for neatness.

Last one takes the results and formats them as you requested.

Edit: If I've understood your comment below then you have all those values in a single row. Slice it up with a pipelined function.

SQL> select * from records;

SUBJ_NO
----------------------------------------------------
CBS405/505 Fourth Week(Wtr Qtr)
BR 404-504-604 Fifth Week(Fall Qtr)
AP490-QB-09 Weekly Sessions(Spr Qtr)
APR501 Fourth Week(Wtr Qtr)
MP 300 Fourth Week(Wtr Qtr)
ACX/501 Fifth Week(Sum Qtr)
ACX 400 Ninth Week(Mas Term)

1 row selected.

SQL> create or replace function get_records return tt_subj_no pipelined as
  lv_recs  records.subj_no%TYPE;
  lv_subj  varchar2(100);
  lv_start number := 0;
  lv_end   number := 10;
begin
  select *
    into lv_recs
    from records;

  lv_end := instr(lv_recs, chr(10));
  while lv_end > 0
  loop
    pipe row(substr(lv_recs, 1, lv_end));
    lv_recs := substr(lv_recs, lv_end + 1);
    lv_end := instr(lv_recs, chr(10));
  end loop;
  pipe row(lv_recs);
end;
/

Function created.

SQL> select * from table(get_records);

COLUMN_VALUE
----------------------------------------------------
CBS405/505 Fourth Week(Wtr Qtr)
BR 404-504-604 Fifth Week(Fall Qtr)
AP490-QB-09 Weekly Sessions(Spr Qtr)
APR501 Fourth Week(Wtr Qtr)
MP 300 Fourth Week(Wtr Qtr)
ACX/501 Fifth Week(Sum Qtr)
ACX 400 Ninth Week(Mas Term)

7 rows selected.

Stick that in the original query and you should be good to go.