League table

from the Artful Common Queries page


Here is a simple soccer league table setup that was developed in the MySQL Forum by J Williams and a contributor named "Laptop Alias". The teams table tracks team ID and name, the games table tracks home and away team IDs and goal totals for each game. The query for standings is built by aggregating a UNION of home team and away team game results:

DROP TABLE IF EXISTS teams, games;
CREATE TABLE teams(id int primary key auto_increment,tname char(32));
CREATE TABLE games(id int primary key auto_increment, date datetime, 
                   hteam int, ateam int, hscore tinyint,ascore tinyint);

INSERT INTO teams VALUES(1,'Wanderers'),(2,'Spurs'),(3,'Celtics'),(4,'Saxons');
INSERT INTO games VALUES
(1,'2008-1-1 20:00:00',1,2,1,0),(2,'2008-1-1 20:00:00',3,4,0,2),
(3,'2008-1-8 20:00:00',1,3,1,1),(4,'2008-1-8 20:00:00',2,4,2,1);
SELECT * FROM teams;
+----+-----------+
| id | tname     |
+----+-----------+
|  1 | Wanderers |
|  2 | Spurs     |
|  3 | Celtics   |
|  4 | Saxons    |
+----+-----------+
SELECT * FROM games;
+----+---------------------+-------+-------+--------+--------+
| id | date                | hteam | ateam | hscore | ascore |
+----+---------------------+-------+-------+--------+--------+
|  1 | 2008-01-01 20:00:00 |     1 |     2 |      1 |      0 |
|  2 | 2008-01-01 20:00:00 |     3 |     4 |      0 |      2 |
|  3 | 2008-01-08 20:00:00 |     1 |     3 |      1 |      1 |
|  4 | 2008-01-08 20:00:00 |     2 |     4 |      2 |      1 |
+----+---------------------+-------+-------+--------+--------+

-- Standings query:
SELECT 
  tname AS Team, Sum(P) AS P,Sum(W) AS W,Sum(D) AS D,Sum(L) AS L,
  SUM(F) as F,SUM(A) AS A,SUM(GD) AS GD,SUM(Pts) AS Pts 
FROM(
  SELECT 
    hteam Team, 
    1 P,
    IF(hscore > ascore,1,0) W,
    IF(hscore = ascore,1,0) D,
    IF(hscore < ascore,1,0) L,
    hscore F,
    ascore A,
    hscore-ascore GD,
    CASE WHEN hscore > ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END PTS
  FROM games
  UNION ALL
  SELECT 
    ateam,
    1,
    IF(hscore < ascore,1,0),
    IF(hscore = ascore,1,0),
    IF(hscore > ascore,1,0),
    ascore,
    hscore,
    ascore-hscore GD,
    CASE WHEN hscore < ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END
  FROM games
) as tot
JOIN teams t ON tot.Team=t.id 
GROUP BY Team 
ORDER BY SUM(Pts) DESC ;
+-----------+------+------+------+------+------+------+------+------+
| Team      | P    | W    | D    | L    | F    | A    | GD   | Pts  |
+-----------+------+------+------+------+------+------+------+------+
| Wanderers |    2 |    1 |    1 |    0 |    2 |    1 |    1 |    4 |
| Spurs     |    2 |    1 |    0 |    1 |    2 |    2 |    0 |    3 |
| Saxons    |    2 |    1 |    0 |    1 |    3 |    2 |    1 |    3 |
| Celtics   |    2 |    0 |    1 |    1 |    1 |    3 |   -2 |    1 |
+-----------+------+------+------+------+------+------+------+------+


Return to the Artful Common Queries page