Sorry, but your calculations are wrong:
hh:mm:ss
00:01:03
00:00:30
00:00:31
00:00:34
00:00:33
00:00:31
00:00:57
00:00:36
--------------
00:01:39
00:05:15
I'd suggest to use
DATEPART[
^] and
DATEADD[
^] functions, as follow:
CREATE TABLE toj
(
jobtime time
);
INSERT INTO toj (jobtime)
VALUES('00:01:03'),
('00:00:30'),
('00:00:31'),
('00:00:34'),
('00:00:33'),
('00:00:31'),
('00:00:57'),
('00:00:36');
SELECT DATEADD(hh, t.TotalHours, DATEADD(n, t.TotalMinutes, DATEADD(ss, t.TotalSeconds, CAST('00:00:00' AS time)))) AS TotalTime
FROM (
SELECT SUM(DATEPART(hh, jobtime)) AS TotalHours,
SUM(DATEPART(n, jobtime)) AS TotalMinutes,
SUM(DATEPART(ss, jobtime)) AS TotalSeconds
FROM toj
) AS t;
Result:
TotalTime
00:05:15.0000000
See:
SqlFiddle[
^]