Using SUM and CASE in Oracle SQL

I framed a query to get the counts grouped by a date column from 2 tables.

select count(*),TO_CHAR(CREATION_DATE,'DD/Mon/YY HH') as Date_TIME,'Table_1' as TABLE_NAME
from TABLE 1
where CREATION_DATE > TO_DATE('01/10/2020','DD/MM/YYYY')
group by TO_CHAR(CREATION_DATE,'DD/Mon/YY HH')
UNION
select count(*),TO_CHAR(CREATION_DATE,'DD/Mon/YY HH') as Date_TIME , 'Table_2' as TABLE_NAME
from TABLE 2
where CREATION_DATE > TO_DATE('01/10/2020','DD/MM/YYYY')
group by TO_CHAR(CREATION_DATE,'DD/Mon/YY HH')

I wanted to get the results from both tables, hence I used UNION. And since I wanted the hourly breakup of counts I grouped by TO_CHAR(CREATION_DATE, 'DD/Mon/YY HH')

The output is something like this:

443 14/Oct/20 04    Table_1
861 14/Oct/20 04    Table_2
371 14/Oct/20 05    Table_1
323 14/Oct/20 06    Table_2

As you can see, I want the output counts to be summed up by the hour from both the tables and give a result like this:

1304 14/Oct/20 04   Table_1
371  14/Oct/20 05   Table_1
323  14/Oct/20 06   Table_2

I can do this excel, but it’s not practical. I am thinking of CASE statements in Oracle but never really implemented it.

Database: Oracle Editor: Toad for Oracle

Answer

Use your current query as data source for the final result:

  SELECT SUM (cnt) sum_cnt, date_time, MAX (table_name) table_name
    FROM (  SELECT COUNT (*) cnt,
                   TO_CHAR (creation_date, 'DD/Mon/YY HH') AS date_time,
                   'Table_1' AS table_name
              FROM TABLE_1
             WHERE creation_date > TO_DATE ('01/10/2020', 'DD/MM/YYYY')
          GROUP BY TO_CHAR (creation_date, 'DD/Mon/YY HH')
          UNION
            SELECT COUNT (*),
                   TO_CHAR (creation_date, 'DD/Mon/YY HH') AS date_time,
                   'Table_2' AS table_name
              FROM TABLE_2
             WHERE creation_date > TO_DATE ('01/10/2020', 'DD/MM/YYYY')
          GROUP BY TO_CHAR (creation_date, 'DD/Mon/YY HH'))
GROUP BY date_time
ORDER BY date_time, table_name