Search code examples
oracle-databasetimetrim

Fix time issue in oracle

Table looks like below:

enter image description here

Expected answer below: enter image description here

Write an oracle query to fix space and return hh:mm format. Consider same time in a same group and return total count

Solution

  • The WITH clause in the query below is just for testing; remove it, and use the actual table and column names in the main query. Note: count is a reserved keyword, so it can't be a column name. I changed it to count_ (with an underscore).

    with
  test_data (srt_tm, count_) as (
    select '1:00'  ,   125 from dual union all
    select '01:00' , 19000 from dual union all
    select ' 01:00',    27 from dual union all
    select '4:00'  , 22000 from dual union all
    select '04:00' ,  1800 from dual union all
    select ' 04:00', 15000 from dual
  )
-- END OF TEST DATA; ACTUAL QUERY BEGINS **BELOW THIS LINE**
select lpad(trim(srt_tm), 5, '0') as srt_tm, sum(count_) as count_
from   test_data
group  by lpad(trim(srt_tm), 5, '0')
order  by srt_tm
;

SRT_TM    COUNT_
------  --------
01:00      19152
04:00      38800