CODEWITHSUNDEEP
sqloracle-database
XD129
XD129

Reputation: 1

Beginner Question (Oracle SQL) - Error with Varchar and SUM

I'm currently using Oracle SQL Developer to learn PL/SQL at university. I'm facing a problem where I had to import a .csv file to a table and have to sum up all elements of a column called TIME (composed of minutes, seconds and milliseconds).

However, the column is consisted of VARCHAR and the format is as of below:

TIME
01:00.250
02:37.408
01:29.803
...

I keep getting an error, mostly because there are non-numeric characters (":" and ".") on the column and hence the sum cannot be done. I checked on other topics and saw people saying to use TO_CHAR, TO_DATE, but none solutions seems to work.

Is there a better/easy approach for this problem?

Any help would be appreciated. :)

Upvotes: 0

Views: 233

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Oracle doesn't have a time data type. So, if you want to sum these, a simplish method is to convert the values to seconds:

select sum( to_number(substr(time, 1, 2)) * 60 +
            to_number(substr(time, 4)) 
          ) as seconds

To convert the value back to a string representation of a number:

select floor(seconds / 60) || ':' || (case when seconds < 10 then '0' || mod(seconds, 60) else '' || mod(seconds, 60) end) as mmss
from (select (sum( to_number(substr(time, 1, 2)) * 60 +
              to_number(substr(time, 4)) 
             ) as seconds
      . . .
     ) s

Upvotes: 2

Related Questions