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,'2025-1-1 20:00:00',1,2,1,0),
(2,'2025-1-1 20:00:00',3,4,0,2),
(3,'2025-1-8 20:00:00',1,3,1,1),
(4,'2025-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 | 2025-01-01 20:00:00 | 1 | 2 | 1 | 0 |
| 2 | 2025-01-01 20:00:00 | 3 | 4 | 0 | 2 |
| 3 | 2025-01-08 20:00:00 | 1 | 3 | 1 | 1 |
| 4 | 2025-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 |
+-----------+------+------+------+------+------+------+------+------+
Last updated 22 May 2024 |