Advanced MySQL time sum

Question

I have MySQL query which works fine and returns multiple rows with time values. I am looking for possibility to sum all those time values and return them as single row.

So query is this:

SELECT TIMEDIFF('24:00:00',(TIMEDIFF('22:00:00',TIME(end)))) AS time
FROM sn_workingtime
WHERE user='magdalena'
AND type='work'
AND start >= '2014-03-01' AND start <= '2014-03-31 23:59:59'
AND (HOUR(`end`) > 22 OR HOUR(`end`) < 4)
AND completed=1

This query returns:

time
02:02:36
03:17:24
03:07:03
02:24:17
03:14:09

I would like this query to return the following sum of all time values in sigle row like this:

time
14:05:29

Is there a way for MySQL to "auto" sum given result set? Thank you!

Answer 1

Try this my friend ,you can convert your time to seconds and then sum all the seconds and finally convert this seconds to time:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(t.time))) as time FROM 
(SELECT TIMEDIFF('24:00:00',(TIMEDIFF('22:00:00',TIME(end)))) AS time
FROM sn_workingtime
WHERE user='magdalena'
AND type='work'
AND start >= '2014-03-01' AND start <= '2014-03-31 23:59:59'
AND (HOUR(`end`) > 22 OR HOUR(`end`) < 4)
AND completed=1)t;

take a look here

Answer 2

Sure there is, just use aggregation functions with no GROUP BY, it will aggregate all rows. Example:

SELECT SUM(time) FROM (
   -- your query goes here
) x