mysql - Sum the time in field type varchar

Question

i have a database with Id(int),name(varchar),Time(varchar)

the time is in this format (HH:MM)

and i want to sum the time for every Id

i tried this but it returns an error in Mysql :

SELECT Id,
SUM( 60 * CONVERT(int, LEFT( Time, 2)) + CONVERT(int, RIGHT(Time, 2)) ) AS 
TotalMinutes
FROM retards
GROUP BY Id;

can someone tell me what's wrong?

this is the error, it's a syntax error as it returns :

MySQL a répondu : Documentation

1064 - Erreur de syntaxe près de 'int, LEFT( total, 2)) + CONVERT(int, RIGHT(total, 2)) ) AS TotalMinutes

FROM re' à la ligne 2

标签： mysqlsum

Answer 1

Wouldn't be better using of direct conversion?

TIME_TO_SEC(CONVERT(`Time`,time)) / 60 AS `TotalMinutes`

full query:

SELECT Id,
SUM(TIME_TO_SEC(CONVERT(`Time`,time)) / 60) AS 
`TotalMinutes`
FROM retards
GROUP BY Id;