CODEWITHSUNDEEP
mysql
user1012135
user1012135

Reputation: 81

Calculate the total time duration on last row in mysql

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 it 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 row to show the total number of duration like:

+----------------------------------------------------+---------------+
| 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 please help me modify the query to show sum of the duration on the next row?

Upvotes: 4

Views: 1524

Answers (3)

mikn
mikn

Reputation: 484

I would ideally relegate that kind of logic to either a second query or the application layer.

But trying to decipher your query to the best of my ability, you could try and see if something like this produces what you want:

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 that Amado had answered with a similar solution, but felt that mine was still relevant.

Upvotes: 0

Amado Martinez
Amado Martinez

Reputation: 439

Try something to this effect:

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 by, with rollup

Upvotes: 4

Jake Feasel
Jake Feasel

Reputation: 16955

I'm not super 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

Upvotes: 1

Related Questions