SELECT id, SUM(win) AS Wins, SUM(loss) AS Losses FROM ( SELECT t.participants.pro_debater AS id, CASE WHEN t.results.winner='pro_debater' THEN 1 ELSE 0 END AS win, CASE WHEN t.results.winner='con_debater' THEN 1 ELSE 0 END AS loss FROM results t UNION ALL SELECT t.participants.con_debater AS id, CASE WHEN t.results.winner='con_debater' THEN 1 ELSE 0 END AS win, CASE WHEN t.results.winner='pro_debater' THEN 1 ELSE 0 END AS loss FROM results t ) GROUP BY id ORDER BY Wins DESC, Losses ASC, id;