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