Counting number of records hour by hour between two dates in oracle

group-by, grouping, oracle, sql

Solution

Note the usage of `trunc` expression with date values. You can omit the `alter session` if you are not running the query in sql*plus.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> SELECT 
       trunc(created,'HH'), 
       count(*) 
     FROM 
       test_table 
     WHERE 
       created > trunc(SYSDATE -2) 
     group by trunc(created,'HH');


TRUNC(CREATED,'HH')   COUNT(*)
------------------- ----------
2012-05-21 09:00:00        748
2012-05-21 16:00:00         24
2012-05-21 17:00:00         12
2012-05-21 22:00:00        737
2012-05-21 23:00:00        182
2012-05-22 20:00:00         16
2012-05-22 21:00:00        293
2012-05-22 22:00:00        610

8 ROWS selected.

Problem

I need a SINGLE query that does this sequence in oracle. ``` select count(*) from table1 where request_time < timestamp'2012-05-19 12:00:00' and (end_time > timestamp'2012-05-19 12:00:00' or end_time=null); select count(*) from table1 where request_time < timestamp'2012-05-19 13:00:00' and (end_time > timestamp'2012-05-19 13:00:00' or end_time=null); select count(*) from table1 where request_time < timestamp'2012-05-19 14:00:00' and (end_time > timestamp'2012-05-19 14:00:00' or end_time=null); select count(*) table1 where request_time < timestamp'2012-05-19 15:00:00' and (end_time > timestamp'2012-05-19 15:00:00' or end_time=null); select count(*) from table1 where request_time < timestamp'2012-05-19 16:00:00' and (end_time > timestamp'2012-05-19 16:00:00' or end_time=null); ``` As you see the hour is increasing one by one. here is the output ``` COUNT(*) 1085 ``` ``` COUNT(*) 1233 ``` ``` COUNT(*) 1407 ``` ``` COUNT(*) 1322 ``` ``` COUNT(*) 1237 ``` I have written a query but it does not give me the right answer! ``` select col1, count(*) from (select TO_CHAR(request_time, 'YYYY-MM-DD HH24') as col1 from table1 where request_time <= timestamp'2012-05-19 12:00:00' and (end_time >= timestamp'2012-05-19 12:00:00' or end_time=null)) group by col1 order by col1; ``` 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: ``` 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 ```

Original source