Calculate total time in last row in mysql

Question

I have this mysql query:

SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name, start.timestamp start, end.timestamp end, timediff(end.timestamp, start.timestamp) duration from user u, user_group ug, ( select *, ( select event_id from event L2 where L2.timestamp>L1.timestamp and L2.user_bannerid=L1.user_bannerid order by timestamp limit 1 ) stop_id from event L1 ) start join event end on end.event_id=start.stop_id where start.status='In' and end.status='Out' and u.user_bannerid = start.user_bannerid and ug.user_bannerid = u.user_bannerid and ug.group_id = start.group_id 

And he shows something like this:

 +----------------------------------------------------+---------------+ | Name | start | end | duration | +----------------------------------------------------+---------------+ | User | 2011-11-24 02:12:05 | 2011-11-24 02:12:20 | 00:00:15 | | User | 2011-11-28 21:46:54 | 2011-11-28 21:53:01 | 00:06:17 | +----------------------------------------------------+---------------+ 

But I want the last line to display the total amount of duration, for example:

 +----------------------------------------------------+---------------+ | Name | start | end | duration | +----------------------------------------------------+---------------+ | User | 2011-11-24 02:12:05 | 2011-11-24 02:12:20 | 00:00:15 | | User | 2011-11-28 21:46:54 | 2011-11-28 21:53:01 | 00:06:17 | | | | | 00:06:32 | +----------------------------------------------------+---------------+ 

Can someone help me modify the query to show the amount of duration in the next line?

Answer 1

I am not very proud of this answer, but it should work:

 SELECT 0 as is_total, CONCAT(u.lastname, ', ', u.firstname) AS Name, start.timestamp start, end.timestamp end, timediff(end.timestamp, start.timestamp) duration from user u, user_group ug, ( select *, ( select event_id from event L2 where L2.timestamp>L1.timestamp and L2.user_bannerid=L1.user_bannerid order by timestamp limit 1 ) stop_id from event L1 ) start join event end on end.event_id=start.stop_id where start.status='In' and end.status='Out' and u.user_bannerid = start.user_bannerid and ug.user_bannerid = u.user_bannerid and ug.group_id = start.group_id UNION SELECT 1, null, null, null, sum(duration) FROM ( SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name, start.timestamp start, end.timestamp end, timediff(end.timestamp, start.timestamp) duration from user u, user_group ug, ( select *, ( select event_id from event L2 where L2.timestamp>L1.timestamp and L2.user_bannerid=L1.user_bannerid order by timestamp limit 1 ) stop_id from event L1 ) start join event end on end.event_id=start.stop_id where start.status='In' and end.status='Out' and u.user_bannerid = start.user_bannerid and ug.user_bannerid = u.user_bannerid and ug.group_id = start.group_id ) total ORDER BY is_total 

Answer 2

Try something like that:

 mysql> select *, timediff(end, start) as diff, sec_to_time(SUM(timediff(end,start))) as sum FROM timet GROUP BY start WITH ROLLUP; +---------------------+---------------------+-------+----------+----------+ | start | end | g | diff | sum | +---------------------+---------------------+-------+----------+----------+ | 2011-11-28 23:00:51 | 2011-11-28 23:00:56 | 0.678 | 00:00:05 | 00:00:05 | | 2011-11-28 23:00:52 | 2011-11-28 23:00:57 | f | 00:00:05 | 00:00:05 | | 2011-11-28 23:00:53 | 2011-11-28 23:00:58 | 0.948 | 00:00:03 | 00:00:05 | | 2011-11-28 23:00:58 | 2011-11-28 23:01:01 | 0.153 | 00:00:03 | 00:00:03 | | NULL | 2011-11-28 23:01:01 | 0.153 | NULL | 00:00:18 | +---------------------+---------------------+-------+----------+----------+ 5 rows in set (0.01 sec) 

Mysql:
Group Using Cumulative File

Answer 3

I would ideally attribute this logic to either the second query or the application layer.

But trying to decrypt your request to the best of your ability, you can try and see if it creates something like this:

 SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name, start.timestamp AS start, end.timestamp AS end, TIME(SUM(TIMEDIFF(end.timestamp, start.timestamp))) AS duration FROM user AS u INNER JOIN user_group AS ug ON u.user_bannerid = ug.user_bannerid INNER JOIN event AS start ON start.user_bannerid = u.user_bannerid AND start.status='In' AND start.group_id = ug.group_id INNER JOIN event AS end ON end.user_bannerid = u.user_bannerid AND end.status='Out' AND start.event_id = end.event_id GROUP BY start.event_id WITH ROLLUP 

http://dev.mysql.com/doc/refman/5.5/en/group-by-modifiers.html

EDIT: Saw Amado responded with a similar solution, but felt mine still mattered.