Combining two queries with UNION ALL isn't summing up columns correctly

Saturday, May 3, 2014

I'm trying to calculate League Standings from a table of Matches.



+----------------------------------+
| Matches |
+----------------------------------+
| id |
| competition_id (FK Competition) |
| season_id (FK Season) |
| home_team_id (FK Team) |
| away_team_id (FK Team) |
| home_score |
| away_score |
| confirmed |
+----------------------------------+


I can correctly calculate the Home League Standings using this query:



SELECT home_team_id AS team_id,
COUNT(*) AS played,
SUM((CASE WHEN home_score > away_score THEN 1 ELSE 0 END)) AS won,
SUM((CASE WHEN away_score > home_score THEN 1 ELSE 0 END)) AS lost,
SUM((CASE WHEN home_score = away_score THEN 1 ELSE 0 END)) AS drawn,
SUM(home_score) AS goalsFor,
SUM(away_score) AS goalsAgainst,
SUM(home_score - away_score) AS goalDifference,
SUM((CASE WHEN home_score > away_score THEN 3 WHEN home_score = away_score THEN 1 ELSE 0 END)) AS points
FROM matches
WHERE league_id = 94
AND season_id = 82
AND confirmed IS NOT NULL
GROUP BY home_team_id
ORDER BY POINTS DESC;


enter image description here


And Away League Standigns using this query:



SELECT away_team_id AS team_id,
COUNT(*) AS played,
SUM((CASE WHEN away_score > home_score THEN 1 ELSE 0 END)) AS won,
SUM((CASE WHEN home_score > away_score THEN 1 ELSE 0 END)) AS lost,
SUM((CASE WHEN home_score = away_score THEN 1 ELSE 0 END)) as drawn,
SUM(away_score) AS goalsFor,
SUM(home_score) AS goalsAgainst,
SUM(away_score - home_score) AS goalDifference,
SUM((CASE WHEN away_score > home_score THEN 3 WHEN away_score = home_score THEN 1 ELSE 0 END)) AS points
FROM matches
WHERE league_id = 94
AND season_id = 82
AND confirmed IS NOT NULL
GROUP BY away_team_id
ORDER BY points DESC;


enter image description here


But combining these two queries using UNION ALL I'm not getting correct result



SELECT * FROM
(
SELECT home_team_id AS team_id,
COUNT(*) AS played,
SUM((CASE WHEN home_score > away_score THEN 1 ELSE 0 END)) AS won,
SUM((CASE WHEN away_score > home_score THEN 1 ELSE 0 END)) AS lost,
SUM((CASE WHEN home_score = away_score THEN 1 ELSE 0 END)) AS drawn,
SUM(home_score) AS goalsFor,
SUM(away_score) AS goalsAgainst,
SUM(home_score - away_score) AS goalDifference,
SUM((CASE WHEN home_score > away_score THEN 3 WHEN home_score = away_score THEN 1 ELSE 0 END)) AS points
FROM matches
WHERE league_id = 94
AND season_id = 82
AND confirmed IS NOT NULL
GROUP BY home_team_id
UNION ALL
SELECT away_team_id AS team_id,
COUNT(*) AS played,
SUM((CASE WHEN away_score > home_score THEN 1 ELSE 0 END)) AS won,
SUM((CASE WHEN home_score > away_score THEN 1 ELSE 0 END)) AS lost,
SUM((CASE WHEN home_score = away_score THEN 1 ELSE 0 END)) as drawn,
SUM(away_score) AS goalsFor,
SUM(home_score) AS goalsAgainst,
SUM(away_score - home_score) AS goalDifference,
SUM((CASE WHEN away_score > home_score THEN 3 WHEN away_score = home_score THEN 1 ELSE 0 END)) AS points
FROM matches
WHERE league_id = 94
AND season_id = 82
AND confirmed IS NOT NULL
GROUP BY away_team_id
) x
GROUP BY team_id
ORDER BY points DESC;


enter image description here


This should be the expected result:


enter image description here


Thanks!







http://ift.tt/1i795kq