Untitled

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