Hello experts, i've created table using Oracle APEX that data exist like this

CREATE TABLE "TR_DAILY_ACTIVITY" 
   ( "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
 "SHIPMENT_ID" NUMBER, 
 "DATE_PERIOD" VARCHAR2(50), 
 "TURNING_TIME" VARCHAR2(50),  
  PRIMARY KEY ("ID")
  USING INDEX  ENABLE
   ) ;

that said the DATE_PERIOD & TURNING_TIME is using varchar2(50) with the sample of the table like this

ID | SHIPMENT_ID | TURNING_TIME | DATE_PERIOD
1  | 111         | 00:05:43     | 13/04/2022
2  | 123         | 01:00:23     | 13/05/2022
3  | 111         | 03:12:00     | 20/06/2022
4  | 111         | 00:02:23     | 10/04/2022
4  | 123         | 10:22:23     | 12/05/2022

I want to sum TURNING_TIME based on SHIPMENT_ID to get the total hours for each

I'm using this query :

SELECT SHIPMENT_ID, sum(to_number(translate(TURNING_TIME,':','.'))) AS sum_AS FROM TR_DAILY_ACTIVITY group by SHIPMENT_ID;

but the console keep saying invalid number

I've tried several combination to cast/replace the data

How can i sum the TURNING_TIME to get the exact hours?



Thanks in advance

Maul