select inst_id,to_char(first_time,'yyyy/mm/dd') "Date",
sum(decode(to_char(first_time,'HH24'),'00',1,0)) H00,sum(decode(to_char(first_time,'HH24'),'01',1,0)) H01,sum(decode(to_char(first_time,'HH24'),'02',1,0)) H02,
sum(decode(to_char(first_time,'HH24'),'03',1,0)) H03,sum(decode(to_char(first_time,'HH24'),'04',1,0)) H04,sum(decode(to_char(first_time,'HH24'),'05',1,0)) H05,
sum(decode(to_char(first_time,'HH24'),'06',1,0)) H06,sum(decode(to_char(first_time,'HH24'),'07',1,0)) H07,sum(decode(to_char(first_time,'HH24'),'08',1,0)) H08,
sum(decode(to_char(first_time,'HH24'),'09',1,0)) H09,sum(decode(to_char(first_time,'HH24'),'10',1,0)) H10,sum(decode(to_char(first_time,'HH24'),'11',1,0)) H11,
sum(decode(to_char(first_time,'HH24'),'12',1,0)) H12,sum(decode(to_char(first_time,'HH24'),'13',1,0)) H13,sum(decode(to_char(first_time,'HH24'),'14',1,0)) H14,
sum(decode(to_char(first_time,'HH24'),'15',1,0)) H15,sum(decode(to_char(first_time,'HH24'),'16',1,0)) H16,sum(decode(to_char(first_time,'HH24'),'17',1,0)) H17,
sum(decode(to_char(first_time,'HH24'),'18',1,0)) H18,sum(decode(to_char(first_time,'HH24'),'19',1,0)) H19,sum(decode(to_char(first_time,'HH24'),'20',1,0)) H20,
sum(decode(to_char(first_time,'HH24'),'21',1,0)) H21,sum(decode(to_char(first_time,'HH24'),'22',1,0)) H22,sum(decode(to_char(first_time,'HH24'),'23',1,0)) H23,
count(1) "Total", round(count(1)/24,2) "Avg Per Hour"
from gv$log_history group by inst_id,to_char(first_time,'yyyy/mm/dd')
order by 1 asc,2 desc