Assign a position value to row based on a SUM

In MySQL 8+, you would just use row_number():

SELECT ROW_NUMBER() OVER (ORDER BY miles DESC) AS position, t.*
FROM (SELECT ROUND(SUM(d.dist_activity_duration *
                       CASE WHEN d.dist_is_distance = 0 THEN s.activity_steps / 2000 
                            WHEN d.dist_is_distance = 1 THEN 1 
                       END), 2)  AS miles, t.team_name AS team_name
      FROM distance d JOIN   
           activities a 
           ON a.id = d.dist_activity_id JOIN
           steps s
           ON s.id = a.steps_id JOIN
           members m   
           ON d.member_id = m.id JOIN
           teams t 
           ON t.id = m.member_team_id
      GROUP BY team_name 
     ) t
ORDER BY miles DESC;

Earlier versions of MySQL support variables but they do not play well with GROUP BY and ORDER BY. The solution is a subquery (as above):

SELECT (@rn := @rn + 1) AS position, 
FROM (SELECT ROUND(SUM(d.dist_activity_duration *
                       CASE WHEN d.dist_is_distance = 0 THEN s.activity_steps / 2000 
                            WHEN d.dist_is_distance = 1 THEN 1 
                       END), 2)  AS miles, t.team_name AS team_name
      FROM distance d JOIN   
           activities a 
           ON a.id = d.dist_activity_id JOIN
           steps s
           ON s.id = a.steps_id JOIN
           members m   
           ON d.member_id = m.id JOIN
           teams t 
           ON t.id = m.member_team_id
      GROUP BY team_name 
      ORDER BY miles DESC
     ) t CROSS JOIN
     (SELECT @rn := 0) params;