Alejandro Beltran Alejandro Beltran - 2 months ago 10
MySQL Question

Mysql group, count and concat laps

I can't resolve this problem (race), I need show the time for each LAP. This is times table

id | race_id | car_num | time |
+-----+-------------+---------+------------+
1 | 8 | 25 | 00:09:05 |
2 | 8 | 33 | 00:09:35 |
3 | 8 | 10 | 00:09:55 |
4 | 8 | 25 | 00:18:15 |
5 | 8 | 33 | 00:19:05 |
6 | 8 | 25 | 00:39:45 |


This tried this output with this query:

SELECT
car_num, COUNT(car_num) as laps, race_id, concat(vlap,'-',time) as times
FROM
(SELECT num_car, concat(time,'-',v1) vlap
FROM tiempos) vti
GROUP BY
car_num


This is output required:

car_num | laps | race_id | times |
+-----+-------------+---------+------------------------------------------------+
25 | 3 | 8 | lap1 00:09:05, lap2 00:18:15, lap3 00:39:45 |
33 | 2 | 8 | lap1 00:09:35, lap2 00:19:05 |
10 | 1 | 8 | lap1 00:09:55 |


I'm dizzy, some idea please

Answer

You can get most of what you want with a simple group_concat():

select car_num, count(*) as laps, race_id, group_concat(time order by id separator ', ' ) as times
from tiempos t
group by car_num, race_id;

If you need the lap number, you can get that using variables:

select car_num, count(*) as laps, race_id, group_concat('lap', rn, ' ', time order by id separator ', ' ) as times
from (select t.*,
             (@rn := if(@t = time, @rn + 1,
                        if(@t := time, 1, 1)
                       ) as rn
      from tiempos t cross join
           (select @rn := 0, @t := '') vars
      order by race_id, car_num, time
     ) t
group by car_num, race_id;
Comments