title: Counting number of records hour by hour between two dates in oracle
body: <p>I need a SINGLE query that does this sequence in oracle.</p>
  
  <pre><code>select count(*) from table1
  where request_time &lt; timestamp'2012-05-19 12:00:00' and (end_time &gt; timestamp'2012-05-19 12:00:00' or end_time=null);
  
  select count(*) from table1
  where request_time &lt; timestamp'2012-05-19 13:00:00' and (end_time &gt; timestamp'2012-05-19 13:00:00' or end_time=null);
  
  select count(*) from table1
  where request_time &lt; timestamp'2012-05-19 14:00:00' and (end_time &gt; timestamp'2012-05-19 14:00:00' or end_time=null);
  
  select count(*) table1
  where request_time &lt; timestamp'2012-05-19 15:00:00' and (end_time &gt; timestamp'2012-05-19 15:00:00' or end_time=null);
  
  select count(*) from table1
  where request_time &lt; timestamp'2012-05-19 16:00:00' and (end_time &gt; timestamp'2012-05-19 16:00:00' or end_time=null);
  </code></pre>
  
  <p>As you see the hour is increasing one by one.
  here is the output</p>
  
  <pre><code>COUNT(*)               
  1085                   
  </code></pre>
  
  <hr>
  
  <pre><code>COUNT(*)               
  1233                   
  </code></pre>
  
  <hr>
  
  <pre><code>COUNT(*)               
  1407                   
  </code></pre>
  
  <hr>
  
  <pre><code>COUNT(*)               
  1322                   
  </code></pre>
  
  <hr>
  
  <pre><code>COUNT(*)               
  1237
  </code></pre>
  
  <hr>
  
  <p>I have written a query but it does not give me the right answer!</p>
  
  <pre><code>select col1, count(*) from
  (select TO_CHAR(request_time, 'YYYY-MM-DD HH24') as col1 from table1
   where request_time &lt;= timestamp'2012-05-19 12:00:00' and (end_time &gt;= timestamp'2012-05-19 12:00:00' or end_time=null))
  group by col1 order by col1;
  </code></pre>
  
  <p>this query gives me a result set that sum of it's count(*) is equal to the first query written above!
  here is the result:</p>
  
  <pre><code>COL1          COUNT(*)               
  ------------- ---------------------- 
  2012-05-19 07      22                     
  2012-05-19 08      141                    
  2012-05-19 09      322                    
  2012-05-19 10      318                    
  2012-05-19 11      282  
  </code></pre>