That is because you changed the query from what I provided.
The UNION clause has some specific requirements.
1) The queries must have the same number of fields (which is why I hard coded some fields with a 0).
2) The "same" fields from the queries must be of the same type
3) The "same" fields must be in the same order.
In the query I provided, homeGames came before awayGames in the two sub queries. However, you changed the second sub query to have awayGames come before homeGames. In truth, you don't even need to name the columns for the second sub query since the results will be based upon the column names from the first query. I only name them to make it easier to read. So, in your version, the awayGames are considered homeGames because they are in the column position associated with homeGames in the first query.
SELECT teamID, SUM(homeGames + awayGames) as gamesPlayed, SUM(homeGames) as homeGames, SUM(awayGames) AS awayGames, SUM(homePoints + awayPoints) AS totalPoints, SUM(homePoints) as homePoints, SUM(awayPoints) AS awayPoints, SUM(pointsAgainst) AS pointsAgainst FROM (SELECT homeTeamID as teamID, COUNT(homeTeamID) as homeGames, 0 AS awayGames, SUM(homePoints) as homePoints, 0 AS awayPoints, SUM(awayPoints) as pointsAgainst FROM results GROUP BY teamID UNION ALL SELECT awayTeamID as teamID, 0 AS homeGames, COUNT(awayTeamID) as awayGames, 0 as homePoints, SUM(awayTeamID) AS awayPoints, SUM(homePoints) as pointsAgainst FROM results GROUP BY teamID ) as t GROUP BY teamID ORDER BY totalPoints DESC, pointsAgainst ASCGo to the full post