Football League Table With Points and Goal Difference

Medium
456.

Football League Table With Points and Goal Difference

Build the league table from the results. A win is worth 3 points, a draw 1, a defeat 0. Every team plays both home and away, and a team appears in the table once it has played at least one match.

Return one row per team with the columns team, played, won, drawn, lost, goals_for, goals_against, goal_difference (goals_for - goals_against) and points. Order the rows by points descending, then goal_difference descending, then goals_for descending, then team alphabetically.

Tables

Table: LeagueMatch

ColumnType
match_idint
match_datedate
home_teamvarchar
away_teamvarchar
home_goalsint
away_goalsint

Primary key: match_id.

One row per match played; goals are never NULL. home_team and away_team are always different clubs.

Example

The rows must come back in the order shown.

LeagueMatch

match_idmatch_datehome_teamaway_teamhome_goalsaway_goals
12024-09-07Goa GullsPune Rovers20
22024-09-08Delhi FalconsKerala Kingfishers11
32024-09-14Pune RoversDelhi Falcons31
42024-09-15Kerala KingfishersGoa Gulls22
52024-09-21Goa GullsDelhi Falcons01
62024-09-22Kerala KingfishersPune Rovers20

Output

teamplayedwondrawnlostgoals_forgoals_againstgoal_differencepoints
Kerala Kingfishers31205325
Goa Gulls31114314
Delhi Falcons311134-14
Pune Rovers310235-23
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Football League Table With Points and Goal Difference

Build the league table from the results. A win is worth 3 points, a draw 1, a defeat 0.

  • Difficulty: Medium
  • Topics: Aggregation, Subqueries
  • Dialect: MySQL
  • Problem: #456

Problem statement

Build the league table from the results. A win is worth 3 points, a draw 1, a defeat 0. Every team plays both home and away, and a team appears in the table once it has played at least one match.

Return one row per team with the columns team, played, won, drawn, lost, goals_for, goals_against, goal_difference (goals_for - goals_against) and points. Order the rows by points descending, then goal_difference descending, then goals_for descending, then team alphabetically.

Tables

Table: LeagueMatch

ColumnType
match_idint
match_datedate
home_teamvarchar
away_teamvarchar
home_goalsint
away_goalsint

Primary key: match_id.

One row per match played; goals are never NULL. home_team and away_team are always different clubs.

Examples

Example 1

LeagueMatch

match_idmatch_datehome_teamaway_teamhome_goalsaway_goals
12024-09-07Goa GullsPune Rovers20
22024-09-08Delhi FalconsKerala Kingfishers11
32024-09-14Pune RoversDelhi Falcons31
42024-09-15Kerala KingfishersGoa Gulls22
52024-09-21Goa GullsDelhi Falcons01
62024-09-22Kerala KingfishersPune Rovers20

Output

teamplayedwondrawnlostgoals_forgoals_againstgoal_differencepoints
Kerala Kingfishers31205325
Goa Gulls31114314
Delhi Falcons311134-14
Pune Rovers310235-23

How to solve Football League Table With Points and Goal Difference

The difficulty is that a match row is about two teams, and the table is about one. The clean way out is to unpivot: write each match twice, once as the home side saw it and once as the away side saw it. In the home view the goals scored are home_goals and conceded away_goals; in the away view they swap. UNION ALL (not UNION, which would merge two identical scorelines of one team) stacks them into side, one row per team per match.

Now every column of the table is an aggregate over a team's rows: COUNT(*) matches played, conditional sums for won/drawn/lost, plain sums for goals, their difference, and points as 3 per win and 1 per draw.

The alternative keeps the matches as they are and joins each team to every match it played in (home_team = team OR away_team = team), choosing the side with IF. It works but every column has to repeat the which-side logic. The ordering is the usual tie-break chain, ending on the team's name so the table is fully determined. Both plans are linear in the number of matches.

Reference solution (MySQL)

WITH side AS (
  SELECT home_team AS team, home_goals AS gf, away_goals AS ga FROM LeagueMatch
  UNION ALL
  SELECT away_team, away_goals, home_goals FROM LeagueMatch
)
SELECT team,
       COUNT(*) AS played,
       SUM(CASE WHEN gf > ga THEN 1 ELSE 0 END) AS won,
       SUM(CASE WHEN gf = ga THEN 1 ELSE 0 END) AS drawn,
       SUM(CASE WHEN gf < ga THEN 1 ELSE 0 END) AS lost,
       SUM(gf) AS goals_for,
       SUM(ga) AS goals_against,
       SUM(gf) - SUM(ga) AS goal_difference,
       SUM(CASE WHEN gf > ga THEN 3 WHEN gf = ga THEN 1 ELSE 0 END) AS points
FROM side
GROUP BY team
ORDER BY points DESC, goal_difference DESC, goals_for DESC, team

Another way

SELECT t.team,
       COUNT(*) AS played,
       SUM(CASE WHEN (m.home_team = t.team AND m.home_goals > m.away_goals) OR (m.away_team = t.team AND m.away_goals > m.home_goals) THEN 1 ELSE 0 END) AS won,
       SUM(CASE WHEN m.home_goals = m.away_goals THEN 1 ELSE 0 END) AS drawn,
       SUM(CASE WHEN (m.home_team = t.team AND m.home_goals < m.away_goals) OR (m.away_team = t.team AND m.away_goals < m.home_goals) THEN 1 ELSE 0 END) AS lost,
       SUM(IF(m.home_team = t.team, m.home_goals, m.away_goals)) AS goals_for,
       SUM(IF(m.home_team = t.team, m.away_goals, m.home_goals)) AS goals_against,
       SUM(IF(m.home_team = t.team, m.home_goals - m.away_goals, m.away_goals - m.home_goals)) AS goal_difference,
       3 * SUM(CASE WHEN (m.home_team = t.team AND m.home_goals > m.away_goals) OR (m.away_team = t.team AND m.away_goals > m.home_goals) THEN 1 ELSE 0 END) + SUM(CASE WHEN m.home_goals = m.away_goals THEN 1 ELSE 0 END) AS points
FROM (SELECT home_team AS team FROM LeagueMatch UNION SELECT away_team FROM LeagueMatch) t
JOIN LeagueMatch m ON m.home_team = t.team OR m.away_team = t.team
GROUP BY t.team
ORDER BY points DESC, goal_difference DESC, goals_for DESC, t.team

← Batters Who Were Out for a Duck This Season · Highest Batting Partnership in Each T20 Match →