CODEWITHSUNDEEP
oracle-database
Winst Howard
Winst Howard

Reputation: 13

Group by hour using Oracle SQL

I have a varchar5 column with times from 

00:00
00:01
00:02
00:03

... all the way to 
23:59

How would I count how many minutes are in an hour? To get the result

00 60
01 60
02 60
and so on...

SQL:

select 24_HOUR_CLOCK
From time table
Group by ...
Order by 24_HOUR_CLOCK ASC

Means to count records

Upvotes: 1

Views: 1149

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39527

I think you can use substr to extract first two characters from the time string and group on that.

select substr(col, 1, 2) hour, count(*) minutes
from your_table
group by substr(col, 1, 2)
order by hour

or find substr inside a subquery as @Mathguy suggested:

select hour,
    count(*) minutes
from (
    select substr(col, 1, 2) hour
    from your_table
    )
group by hour
order by hour

Upvotes: 1

Related Questions