sql - display the count of the rows created in each hour in last day with 0 count when rows are not created -
i have table below. date |name |number 5/11/2016 17:00:50| abc |123 5/11/2016 18:00:05| def |456 5/11/2016 18:15:00 |ghi |789 i have display count of rows created in each hour in last day. , when no rows created in hour, should display count 0. have limitation of not use pl/sql or multiple queries. i have tried below query, problem is howing both rows of count 0 , count select trunc(sysdate ,'hh') - level/24 dates , 0 count dual connect level <= 24 union select trunc (date, 'hh') dates, count(*) count table date> sysdate -1 group trunc (date, 'hh') . date |count 04-nov-16 08.00.00 |0 04-nov-16 09.00.00 |0 04-nov-16 10.00.00 |0 04-nov-16 11.00.00 |0 05-nov-16 12.00.00 |0 05-nov-16 01.00.00 |0 05-nov-16 02.00.00 |0 05-nov-16 03.00.00 |0 05-nov-16 04.00.00 |0 05-nov-16 05.00.00 |0 05-nov-16 06.00.00 |0 05-nov-16 07.00.00 |0 05-nov-16 08.00.00 |0 05-nov-16 09.00.00 |0 05-nov-16 10.00.00 |0...