SUM minutes SQL server

Question

HI guys, My litle problem goes like this : I have this columns : PHONE_NR , TIME ( time field ), Meaning the calling telephone number and call duration. I need to group phone nr and sum the minutes. Filds looks like this :

 nr          time 
726028xxx 00:07:07 
735560css 00:07:37 
726028xxx 00:07:55 

Answer 1

SELECT PHONE_NR, SUM(DATEPART(minute, TIME)) FROM [table] GROUP BY PHONE_NR;

As far as I know this should work for both SQL Server DATETIME and the 2008 TIME data-types.

Example:

DECLARE @tbl TABLE
(
    phone_nr varchar(10),
    call_time TIME
);

INSERT INTO @tbl VALUES ('726028xxx', '00:07:07');
INSERT INTO @tbl VALUES ('735560css', '00:07:37');
INSERT INTO @tbl VALUES ('726028xxx', '00:07:55');

SELECT phone_nr, SUM(DATEPART(minute, call_time)) AS total_call_time FROM @tbl GROUP BY phone_nr;

Results in:

phone_nr  | minutes
726028xxx | 14
735560css | 7

EDIT:

This version is identical to the above, except it also takes into account the seconds and hours (e.g. 1hr 7min 07secs = 67.117 minutes) so it's much more accurate.

SELECT 
    phone_nr, 
    SUM(CAST(DATEPART(second, call_time) + (DATEPART(minute, call_time) * 60) + (DATEPART(hour, call_time) * 3600) AS decimal) / 60) AS total_call_time
FROM 
    @tbl
GROUP BY
    phone_nr;

Results in the following if the first record was 01:07:07.

phone_nr  | minutes
726028xxx | 75.033332
735560css | 7.616666

Answer 2

You can use DATEDIFF to get the total number of minutes that have passed since time "zero"

SELECT
 [PHONE_NR],
 SUM(DATEDIFF(minute, '00:00:00', [TIME]))
FROM
 [YourTable]
GROUP BY
 [PHONE_NR]