Skip to content
Advertisement

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 ?

Advertisement

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

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement