$sql = "SELECT *,
SUM(IF (is_battle = 1, 1, 0)) battles,
SUM(IF (is_battle = 1 AND winner = fleet_side AND (engaged_in IS NULL OR battle_id != engaged_in), 1, 0)) battle_victories,
SUM(IF (is_battle = 0, 1, 0)) skirmishes,
SUM(IF (is_battle = 0 AND winner = fleet_side AND (engaged_in IS NULL OR battle_id != engaged_in), 1, 0)) skirmish_victories,
2*
SUM(IF (is_battle = 1 AND winner = fleet_side AND (engaged_in IS NULL OR battle_id != engaged_in) AND ran_away_factor IS NULL, factor, 0))+
SUM(IF (is_battle = 0 AND winner = fleet_side AND (engaged_in IS NULL OR battle_id != engaged_in) AND ran_away_factor IS NULL, factor, 0)) AS points
FROM
(
   SELECT x.character_id, given_name, family_name, rank, side,
   is_battle, x.battle_id, winner, fleet_id, fleet_side, engaged_in, factor, ran_away_factor
   FROM
      (
      # select only 1 row by character and by battle, with priority to a fleet that was not lost in the battle
      # (for the case a character had several fleets engaged in the battle)
      SELECT character_id, battle_id, MIN(IF (engaged_in IS NULL OR battle_id != engaged_in, 0, 1)) AS min_priority FROM fleet, fleet_meeting WHERE fleet.fleet_id = fleet_meeting.fleet1_id GROUP BY character_id, battle_id
      )
      AS x
      
      INNER JOIN
      (
      # select all the interesting fields, with multiple rows by character x battle, and a time clause
      SELECT c.character_id, given_name, family_name, rank, side,
      b.is_battle, b.battle_id, b.winner, f.fleet_id, f.fleet_side, f.engaged_in, IF (f.engaged_in IS NULL OR b.battle_id != f.engaged_in, 0, 1) AS priority, IF (f.fleet_side = 0, b.fpa_ships_engaged/b.ge_ships_engaged, b.ge_ships_engaged/b.fpa_ships_engaged) AS factor
      FROM character c, fleet f, (SELECT DISTINCT fleet1_id, battle_id FROM fleet_meeting) fm, battle b WHERE c.character_id = f.character_id AND f.fleet_id = fm.fleet1_id AND fm.battle_id = b.battle_id
      $time_clause
      )
      AS y ON x.character_id = y.character_id AND x.battle_id = y.battle_id AND x.min_priority = y.priority
      
      LEFT JOIN
      (
      # select the characters whose fleet ran away before the final cease fire
      SELECT character_id, engage.battle_id, IF(SUM(nb_engage)=SUM(nb_disengage),0,NULL) AS ran_away_factor FROM
      (SELECT battle_event.battle_id, fleet_id, COUNT(event_id) AS nb_engage FROM battle_event, (SELECT battle_id, MAX(event_id) AS max_event_id FROM battle_event WHERE type = 'cease_fire' GROUP BY battle_id) AS cease_fire_events WHERE battle_event.battle_id = cease_fire_events.battle_id AND event_id < max_event_id AND type = 'engage' GROUP BY battle_event.battle_id, fleet_id) AS engage,
      (SELECT battle_event.battle_id, fleet_id, COUNT(event_id) AS nb_disengage FROM battle_event, (SELECT battle_id, MAX(event_id) AS max_event_id FROM battle_event WHERE type = 'cease_fire' GROUP BY battle_id) AS cease_fire_events WHERE battle_event.battle_id = cease_fire_events.battle_id AND event_id < max_event_id AND type = 'disengage' GROUP BY battle_event.battle_id, fleet_id) AS disengage,
      fleet
      WHERE engage.battle_id = disengage.battle_id AND engage.fleet_id = disengage.fleet_id AND engage.fleet_id = fleet.fleet_id GROUP BY character_id, battle_id
      )
      AS z ON x.character_id = z.character_id AND x.battle_id = z.battle_id
      
   GROUP BY x.character_id, x.battle_id
) AS zzz
GROUP BY character_id
ORDER BY points DESC, character_id DESC";