League table in MySQL

-- ---------------------------------------------------------------------------
-- League table
--
-- from the Artful Common Queries page
-- http://www.artfulsoftware.com/infotree/qrytip.php?id=804
-- ---------------------------------------------------------------------------

-- 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 |
-- +-----------+------+------+------+------+------+------+------+------+