CODEWITHSUNDEEP
sqloracle-databasecountsum
Nomad
Nomad

Reputation: 268

Oracle SQL Count Records per Hour

So, what I'm trying to do basically is count the amount of shipments, per hour, per carrier, but I'm having difficulties getting it to sum in the columns I want. The goal is to instead of having 1 or 2 under Hour 1 or Hour 2 it would have sum(total shipments). Essentially 24 hours so we could see trends in time of day...

Code:

    select router_destination_code, 
case when to_char(app_last_updated_date_utc, 'HH24') = '00' then '1' else NULL end as "Hour 1",
case when to_char(app_last_updated_date_utc, 'HH24') = '01' then '2' else NULL end as "Hour 2",
case when to_char(app_last_updated_date_utc, 'HH24') = '02' then '3' else NULL end as "Hour 3",
--case when app_last_updated_date_utc between 'dec/07/2013 16:00:00' and 'dec/14/2013 17:00:00' then count(Router_Destination_code) else NULL end as "Hour_1"
count(Router_Destination_code) as Shipments
from booker.routing_container_history
where 
app_last_updated_by_module in ('ManualSlam', 'slam')
and app_last_updated_date_utc between 'dec/07/2013 16:00:00' and 'dec/14/2013 16:00:00'
group by 
router_destination_code, 
case when to_char(app_last_updated_date_utc, 'HH24') = '00' then '1' else NULL end, 
case when to_char(app_last_updated_date_utc, 'HH24') = '01' then '2' else NULL end, 
case when to_char(app_last_updated_date_utc, 'HH24') = '02' then '3' else NULL end
order by 
case when to_char(app_last_updated_date_utc, 'HH24') = '00' then '1' else NULL end, 
case when to_char(app_last_updated_date_utc, 'HH24') = '01' then '2' else NULL end, 
case when to_char(app_last_updated_date_utc, 'HH24') = '02' then '3' else NULL end, 
count(Router_Destination_code) desc;

Output:

enter image description here

But, the Goal is to have the shipments underneath the hour.

Thank you.

Here is a new approach I did.. But it is showing lots of zeros, and would like it only to show numbers the whole way across.

select router_destination_code, 
count(case when to_char(app_last_updated_date_utc, 'HH24') = '16' then router_destination_code else NULL end) as "Hour 1",
count(case when to_char(app_last_updated_date_utc, 'HH24') = '17' then router_destination_code else NULL end) as "Hour 2"
--case when app_last_updated_date_utc between 'dec/07/2013 16:00:00' and 'dec/14/2013 17:00:00' then count(Router_Destination_code) else NULL end as "Hour_1"
--count(Router_Destination_code) as Shipments
from booker.routing_container_history
where 
app_last_updated_by_module in ('ManualSlam', 'slam')
and app_last_updated_date_utc between 'dec/07/2013 16:00:00' and 'dec/14/2013 16:00:00'
group by 
router_destination_code, 
case when to_char(app_last_updated_date_utc, 'HH24') = '16' then router_destination_code else NULL end,
case when to_char(app_last_updated_date_utc, 'HH24') = '17' then router_destination_code else NULL end
order by 
case when to_char(app_last_updated_date_utc, 'HH24') = '16' then router_destination_code else NULL end,
case when to_char(app_last_updated_date_utc, 'HH24') = '17' then router_destination_code else NULL end,
count(Router_Destination_code) desc;

Upvotes: 0

Views: 1561

Answers (1)

MiGro
MiGro

Reputation: 1511

I do not know if I understood you well but you may try to just extract hour from date and then group by:

Check this fiddle (sorry for bad syntax): http://sqlfiddle.com/#!4/a0a97/6

create table a (id number, data date);
insert into a (id, data)
values (1, to_date( '2013-01-01 13:12:01', 'yyyy-mm-dd hh24:mi:ss'));
insert into a (id, data)
values (1, to_date( '2013-01-01 13:12:01', 'yyyy-mm-dd hh24:mi:ss'));
insert into a (id, data)
values (1, to_date( '2013-01-01 14:12:01', 'yyyy-mm-dd hh24:mi:ss'));

select to_char(data,'HH24'), count(1) from a group by to_char(data,'HH24');

Upvotes: 1

Related Questions