Hello,
Create table and insert statement are below:
CREATE TABLE message_cnt
(
CDATE DATE,
count NUMBER
);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 05:00:00','mm/dd/yyyy hh24:mi:ss'),10);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 19:00:00','mm/dd/yyyy hh24:mi:ss'),8);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 10:00:00','mm/dd/yyyy hh24:mi:ss'),10);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 18:00:00','mm/dd/yyyy hh24:mi:ss'),8);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 15:00:00','mm/dd/yyyy hh24:mi:ss'),47);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 16:00:00','mm/dd/yyyy hh24:mi:ss'),15);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 23:00:00','mm/dd/yyyy hh24:mi:ss'),8);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 14:00:00','mm/dd/yyyy hh24:mi:ss'),11);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 22:00:00','mm/dd/yyyy hh24:mi:ss'),9);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 02:00:00','mm/dd/yyyy hh24:mi:ss'),6);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 04:00:00','mm/dd/yyyy hh24:mi:ss'),8);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 09:00:00','mm/dd/yyyy hh24:mi:ss'),8);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 17:00:00','mm/dd/yyyy hh24:mi:ss'),9);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 20:00:00','mm/dd/yyyy hh24:mi:ss'),7);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 00:00:00','mm/dd/yyyy hh24:mi:ss'),6);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 13:00:00','mm/dd/yyyy hh24:mi:ss'),10);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 01:00:00','mm/dd/yyyy hh24:mi:ss'),8);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 08:00:00','mm/dd/yyyy hh24:mi:ss'),9);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 11:00:00','mm/dd/yyyy hh24:mi:ss'),8);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 12:00:00','mm/dd/yyyy hh24:mi:ss'),9);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 21:00:00','mm/dd/yyyy hh24:mi:ss'),9);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 06:00:00','mm/dd/yyyy hh24:mi:ss'),10);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 07:00:00','mm/dd/yyyy hh24:mi:ss'),10);
insert into message_cnt (cdate, count) values (to_date('10/12/2017 03:00:00','mm/dd/yyyy hh24:mi:ss'),8);
My Requirment is get sum of the count by three hour interval.
Expected output:
I make this script, it works but I think there will be pretty good solutions for this situation...
select '10/12/2017 00:00:00 to 10/12/2017 03:00:00' as cdate, sum(count) from message_cnt where cdate > trunc(sysdate) + 0/24 and cdate <= trunc(sysdate) + 3/24
union all
select '10/12/2017 03:00:00 to 10/12/2017 06:00:00' as cdate, sum(count) from message_cnt where cdate > trunc(sysdate) + 3/24 and cdate <= trunc(sysdate) + 6/24
union all
select '10/12/2017 06:00:00 to 10/12/2017 09:00:00' as cdate, sum(count) from message_cnt where cdate > trunc(sysdate) + 6/24 and cdate <= trunc(sysdate) + 9/24
union all
select '10/12/2017 09:00:00 to 10/12/2017 12:00:00' as cdate, sum(count) from message_cnt where cdate > trunc(sysdate) + 9/24 and cdate <= trunc(sysdate) + 12/24
union all
select '10/12/2017 12:00:00 to 10/12/2017 15:00:00' as cdate, sum(count) from message_cnt where cdate > trunc(sysdate) + 12/24 and cdate <= trunc(sysdate) + 15/24
union all
select '10/12/2017 15:00:00 to 10/12/2017 18:00:00' as cdate, sum(count) from message_cnt where cdate > trunc(sysdate) + 15/24 and cdate <= trunc(sysdate) + 18/24
union all
select '10/12/2017 18:00:00 to 10/12/2017 21:00:00' as cdate, sum(count) from message_cnt where cdate > trunc(sysdate) + 18/24 and cdate <= trunc(sysdate) + 21/24
union all
select '10/12/2017 21:00:00 to 10/13/2017 00:00:00' as cdate, sum(count) from message_cnt where cdate > trunc(sysdate) + 21/24 and cdate <= trunc(sysdate) + 1
For cdate in output it is not important to like mine .
It will also be three column here like this(from_date, to_date, sum(count))
i just want to see interval(three hour) and sum(*) as output,
Thanks...
Message was edited by: Sevdimali Isayev insert script updated.