CodeDump
Facu2138 Facu2138 - 10 months ago 63
SQL Question

SUM() of time column using MySQL

I'm trying to create a query to sum time grouped by user and the result should be in minutes.

contents of table:

duration user
00:06:00 user1
00:06:00 user2
00:04:00 user3
00:01:00 user55
00:02:00 user1
00:01:00 user2
00:01:00 user55


Result expected:

duration user
8 user1
7 user2
4 user3
2 user55


This is the closer I can get:

SELECT user, SEC_TO_TIME( SUM( TIME_TO_SEC(
duration
) ) ) AS totaltime from salientes group by user


However this outputs the result as 000:00:00 and for some cases, this ends on 838:59:59.

Any help will be much appreciated.

Mahesh Madushanka Mahesh Madushanka
Answer 
    mysql> SELECT MINUTE(SEC_TO_TIME(SUM(TIME_TO_SEC( duration)))) 
  as tt ,user from tt group by user;
    +------+--------+
    | tt   | user   |
    +------+--------+
    |    8 | user1  |
    |    7 | user2  |
    |    4 | user3  |
    |    2 | user55 |
    +------+--------+
    4 rows in set (0.00 sec)
Share