Below is the sample code for illustration:
with val (id,dt) as (
select 1, TO_TIMESTAMP('2017/03/22 10:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual union all
select 2, TO_TIMESTAMP('2017/03/22 10:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual union all
select 3, TO_TIMESTAMP('2017/03/22 12:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual union all
select 4, TO_TIMESTAMP('2017/03/22 12:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual union all
select 5, TO_TIMESTAMP('2017/03/22 15:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual union all
select 6, TO_TIMESTAMP('2017/03/23 10:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual union all
select 7, TO_TIMESTAMP('2017/03/23 11:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual union all
select 8, TO_TIMESTAMP('2017/03/23 11:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual union all
select 9, TO_TIMESTAMP('2017/03/23 14:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual union all
select 10, TO_TIMESTAMP('2017/03/23 14:13:18', 'YYYY/MM/DD HH24:MI:SS') from dual
) select nvl(to_char(trunc(dt, 'HH'), 'DD-MON-RR HH24'), 'SUM = ') AS hourly_date,count(id) from val
group by rollup (trunc(dt, 'HH'))
order by 1
Below is the output for above SQL:
Is there any way to produce Totals for each day, rather than grand total