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...