Query 7. Head-to-head record by club pairing

Head-to-head record by club pairing.

capture the result grid in phpMyAdmin after running the query.

SELECT
    s.season_label,
    ht.team_name AS team_a,
    at.team_name AS team_b,
    SUM(CASE WHEN g.home_team_id = ht.team_id AND g.home_score > g.away_score THEN 1
             WHEN g.away_team_id = ht.team_id AND g.away_score > g.home_score THEN 1
             ELSE 0 END) AS team_a_wins,
    SUM(CASE WHEN g.home_team_id = at.team_id AND g.home_score > g.away_score THEN 1
             WHEN g.away_team_id = at.team_id AND g.away_score > g.home_score THEN 1
             ELSE 0 END) AS team_b_wins,
    COUNT(*) AS total_games
FROM games g
INNER JOIN seasons s ON s.season_id = g.season_id
INNER JOIN teams ht ON ht.team_id = g.home_team_id
INNER JOIN teams at ON at.team_id = g.away_team_id
GROUP BY s.season_id, s.season_label, ht.team_id, ht.team_name, at.team_id, at.team_name
HAVING COUNT(*) >= 2
ORDER BY s.season_id DESC, total_games DESC, team_a ASC;