Hospital Llano Hospital Llano - 2 months ago 8
MySQL Question

Mysql Group concat and subtraction values

I tried to calculate the difference in values ​​obtained from a mysql query where data values ​​are extracted in TIME format time laps of competition . the goal is to get the lap times between arrivals for each lap. This is the table

Times
.

id number time
---------+----------------+--------------------
1 9 00:00:02.000000
2 10 00:00:14.000000
3 9 00:11:09.000000


Mysql

SELECT
t.number, count(*) as laps,
group_concat(time order by t.id separator ',' ) as times,
SEC_TO_TIME(SUM(TIME_TO_SEC(`time`))) as total
FROM
times t
GROUP BY
number
ORDER BY
laps DESC, total ASC


The output of this query is:

number laps times total
--------------------------------------------------------------
9 2 00:00:02,00:00:14 00:00:16.000000
10 1 00:11:09 00:11:09.000000


Now, what I need is to get the length of time of each lap, WITH SUBTRACTIONS.
Any suggestion please. Thanks to this result:

number laps times total
--------------------------------------------------------------
9 2 00:00:02,00:00:12 00:00:14.000000
10 1 00:11:09 00:11:09.000000

Answer

Seems you have total times stored and want to calculate lap times. One way to do this is using a subquery;

SELECT t.number, COUNT(1) laps, 
  GROUP_CONCAT(SEC_TO_TIME(time) ORDER BY t.id) times,
  SEC_TO_TIME(SUM(time)) total
FROM (
  SELECT t1.id, t1.number, 
    TIME_TO_SEC(t1.time) - COALESCE(SUM(TIME_TO_SEC(t2.time)), 0) time
  FROM times t1 
  LEFT JOIN times t2 ON t1.number = t2.number AND t2.id < t1.id
  GROUP BY t1.id, t1.number, t1.time
) t
GROUP BY number

The subquery calculates the lap times by subtracting all times for the same number with a smaller id from the total time for the end of each lap. The outer query does the formatting of the lap times in the format you're looking for.

An SQLfiddle to test with.

Comments