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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With