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 05-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 05.00.00  |1 05-nov-16 06.00.00  |0 05-nov-16 06.00.00  |2 05-nov-16 07.00.00  |0 

i working on oracle 11g , have display results in single query.

you notice didn't order hh why output looks odd. also, 11 on 5 nov. 0 hours looking back!

the first cte, inputs, testing (it not part of solution). in rest of query, replace inputs actual table name, , same column names (which better not include date , number!)

with      inputs ( dt, name, nbr ) (      select to_date('5/11/2016 17:00:50', 'dd/mm/yyyy hh24:mi:ss'), 'abc', 123 dual         union      select to_date('5/11/2016 18:00:05', 'dd/mm/yyyy hh24:mi:ss'), 'def', 456 dual         union      select to_date('5/11/2016 18:15:00', 'dd/mm/yyyy hh24:mi:ss'), 'ghi', 789 dual      ), -- end test data; solution (sql query) begins here, add word before d (hh)      d ( hh ) (         select trunc(sysdate, 'hh') - level/24 dual connect level <= 24      ) select d.hh, coalesce(i.ct, 0) ct   d left outer join (                       select trunc(dt, 'hh') hh, count(*) ct                         inputs                        dt >= sysdate - 1                       group trunc(dt, 'hh')                                           )          on d.hh = i.hh ;  hh                          ct ------------------- ---------- 2016-11-04 06:00:00          0 2016-11-04 21:00:00          0 2016-11-04 19:00:00          0 2016-11-04 23:00:00          0  [............................]  2016-11-04 10:00:00          0 2016-11-04 22:00:00          0   24 rows selected