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

Question

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  

Fulani

Answer 1

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.

Broaden

Thanks but this gives me the same result that I have written by group by. The question is: I need sum of this for hour 22:00:00 which in your example would be 748+24+12+737+182+16+293+610=2622 and for hour 16:00:00 it should be 748+24=772 and so on... – Fulani

I think i don't understand you. – Polack

@Heidarzadeh: If you are talking about running total of COUNT(*), try ipip's solution with count(*) replaced with sum(count(*)) over (order by trunc(created,'HH')). – Noto

@Andriy M I tried you solution but got this error "not a single-group group function" – Fulani

@Heidarzadeh: Are you sure that trunc(created,'HH') was still included in group by? – Noto

Yes i'm sure that I put this at the end of my code: group by trunc(request_time,'HH'); – Fulani

When I use it without sum function, no error but when I add sum function i see the error. – Fulani

Just to make it clear - you did sum(*) instead of count(*)? – Polack

No actually, as Andriy M mentioned in the comment above, I used sum(count()) instead of count(). – Fulani

@Heidarzadeh: Sorry, if this sounds stupid, but... did you replace count(*) with just sum(count(*)) or with sum(count(*)) over (order by trunc(created,'HH'))? It should be the latter. – Noto

It works! Many thanks to Andriy M and ipip. Sorry for any inconvenience, cause I'm not a sql guru! now I have another requirement that will make this a little harder. I will ask it in another thread. – Fulani

What would be the MySQL query for the above ? – Dewdrop

Answer 2

Your individual queries seem to be matching overlapping sets of records. It would help if you included some sample data in your question, but I can guess...

For example, all the records which have an end_time=null and a request_time=2012-05-19 13:30:00 will be counted by both the first and second queries; but they will only be counted once in your "overall" query.

Maybe you meant to query on a date range on request_time, instead of having an open-ended predicate like request_time < timestamp'2012-05-19 12:00:00'?

Travail

"will be counted by both the first and second queries" yes i need this. something like fibonacci! count of this hour is count of previous hours + count of this hour. – Fulani

I have two dates and i want to calculate this query between two dates. then i want to extend this query to get the result by day, month and even year! – Fulani

Answer 3

For Oracle database its working as expected.

SELECT to_char(updated,'DD-MM-YYYY HH'), count(*) FROM customer WHERE trunc(updated) >= to_Char('02-JUL-2017') And trunc(updated) <= to_Char('02-JUL-2017') group by to_char(updated,'DD-MM-YYYY HH')

Shivery

Answer 4

Try this

select TO_CHAR(request_time, 'HH24') as "hourOfDay",count(*)as
"numOfLogin", TO_CHAR(request_time, 'DD') as "date" from table1 
where request_time<= timestamp'2017-08-04 23:59:59' and
(request_time>= timestamp'2017-08-03 00:00:01' ) group by
TO_CHAR(request_time, 'HH24'),TO_CHAR(request_time, 'DD');

Compute

Answer 5

I had to do the same thing you were looking to do. Here's the logic I came up with

select count(0), hours
from
(select to_char(from_date + ((to_number(column_value)-1)/24), 'yyyy-mm-dd hh24')||':00:00' hours
  from table1,
       xmltable ('for $i in 1 to xs:int(.) return $i'
                 passing xmlelement(e, extract (hour from cast(to_date as timestamp)-cast(from_date as timestamp))+2))
where change_date > sysdate - 1
)
group by hours
order by count(0) desc;

Hydrastine