Oracle DB sql group the table_names patterns into a group

I am trying to classify certain patterns of tables in individual groups and display the results this is fine i included a case statements and classified it but how to group the counts , let provide the sql logic and existing results and expected results so that it will be understood.

database is Oracle 11g

SQL Query

select DISTINCT OBJECT_NAME ,count(*) as COUNTS,
CASE 
    WHEN OBJECT_NAME LIKE '%AB_ABC%'
    THEN 'ABC'
    WHEN OBJECT_NAME LIKE 'AB_BBC%'
    THEN 'BBC'
    WHEN OBJECT_NAME LIKE 'AB_CNBC%'
    THEN 'CNBC'
    WHEN OBJECT_NAME LIKE 'AB_PTV%'
    THEN 'PTV'
    WHEN OBJECT_NAME LIKE 'AB_CNN%'
    THEN 'CNN'
    WHEN OBJECT_NAME LIKE '%TMP%'
    THEN 'TEMP'
    WHEN OBJECT_NAME LIKE '%TEMP%'
    THEN 'TEMP'
    ELSE        
      'OTHER'
      END
       AS TABLE_GROUP
       from dba_objects
where owner='SCHEMA_NAME'
and object_type='TABLE'
/*and OBJECT_NAME not like '%AB_ABC%'
and OBJECT_NAME not like '%AB_BBC%'
and OBJECT_NAME not like '%AB_CNBC%'
and OBJECT_NAME not like '%AB_PTV%'
and OBJECT_NAME not like '%AB_CNN%'
and OBJECT_NAME not like '%TEMP%'
and OBJECT_NAME not like '%SKY_TV%'*/
group by OBJECT_NAME
order by object_name;

EXISTING RESULTS

OBJECT_NAME TABLE_GROUP COUNTS
AB_ABC      ABC          1
AB_BBC      BBC          1
AB_CNBC     CNBC         1
AB_PTV      PTV          1
AB_CNN      CNN          1

EXPECTED RESULTS

OBJECT_NAME TABLE_GROUP COUNTS
AB_ABC      ABC          120
AB_BBC      BBC          130
AB_CNBC     CNBC         20
AB_PTV      PTV          80
AB_CNN      CNN          600

how to achieve the expected results ? using any WITH CTE ?

please suggest a best possible way ?

Answer

Aggregate by the case expression:

select count(*) as COUNTS,
    CASE 
        WHEN OBJECT_NAME LIKE '%AB_ABC%'
        THEN 'ABC'
        WHEN OBJECT_NAME LIKE 'AB_BBC%'
        THEN 'BBC'
        WHEN OBJECT_NAME LIKE 'AB_CNBC%'
        THEN 'CNBC'
        WHEN OBJECT_NAME LIKE 'AB_PTV%'
        THEN 'PTV'
        WHEN OBJECT_NAME LIKE 'AB_CNN%'
        THEN 'CNN'
        WHEN OBJECT_NAME LIKE '%TMP%'
        THEN 'TEMP'
        WHEN OBJECT_NAME LIKE '%TEMP%'
        THEN 'TEMP'
        ELSE  'OTHER'
    END AS TABLE_GROUP
       from dba_objects
where owner='SCHEMA_NAME' and object_type='TABLE'
/*and OBJECT_NAME not like '%AB_ABC%'
and OBJECT_NAME not like '%AB_BBC%'
and OBJECT_NAME not like '%AB_CNBC%'
and OBJECT_NAME not like '%AB_PTV%'
and OBJECT_NAME not like '%AB_CNN%'
and OBJECT_NAME not like '%TEMP%'
and OBJECT_NAME not like '%SKY_TV%'*/
group by     CASE 
        WHEN OBJECT_NAME LIKE '%AB_ABC%'
        THEN 'ABC'
        WHEN OBJECT_NAME LIKE 'AB_BBC%'
        THEN 'BBC'
        WHEN OBJECT_NAME LIKE 'AB_CNBC%'
        THEN 'CNBC'
        WHEN OBJECT_NAME LIKE 'AB_PTV%'
        THEN 'PTV'
        WHEN OBJECT_NAME LIKE 'AB_CNN%'
        THEN 'CNN'
        WHEN OBJECT_NAME LIKE '%TMP%'
        THEN 'TEMP'
        WHEN OBJECT_NAME LIKE '%TEMP%'
        THEN 'TEMP'
        ELSE  'OTHER'
    END