SELECT tire_id,
Avg(relative_speed) as relative_speed_laps_matching_tire,
0.803418 as all_other_relative_speed_laps,
Avg(relative_speed) - 0.803418 as final_speed
FROM (
SELECT Ifnull(vi.tire_id_front, vehicles.tire_id_front) AS tire_id,
relative_speed
FROM `laps`
INNER JOIN `track_variations`
ON `track_variation_id` = `track_variations`.`id`
LEFT JOIN `vi`
ON `vi_id` = `vi`.`id`
INNER JOIN `vehicle_years`
ON `vehicle_years`.`id` = `laps`.`vehicle_year_id`
INNER JOIN `vehicles`
ON `vehicles`.`id` = `vehicle_years`.`vehicle_id`
WHERE `length` > 0
AND `track_variations`.`flagged` = 0
AND `wet` = 0
AND `novice` = 0
AND `laps`.`flagged` = 0
) AS tire_speed
WHERE `tire_id` IN ( 15, 8, 6, 3, 7 )
GROUP BY tire_id