How to sum up columns by a foreign key that's in two columns?

Question

Currently I'm achieving the result below using a table called league_standing and updating it after every match. I'd like to be able to do with one query against the table matches.

Teams play each other twice, both home and away. Notice how team_id is in the two columns home_team_id and away_team_id

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

Here is what I have tried but failed:

select team.name, HomePoints + AwayPoints points
from team join (
    select team.id, 
        sum(case when home.home_score > home.away_score then 3
            when home.home_score = home.away_score then 1 else 0 end) HomePoints,
        sum(case when away.away_score > away.home_score then 3 else 0 end) AwayPoints
    from team 
    join matches home on team.id = home.home_team_id
    join matches away on team.id = away.away_team_id
    WHERE home.league_id = 94
        AND home.season_id = 82
        AND home.confirmed IS NOT NULL 
    group by id
) temp on team.id = temp.id
order by points desc;

It's giving me the wrong points:

and this one gives me the correct result of league_standings of home team only

SELECT * FROM 
(
    SELECT team.name, 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
    INNER JOIN team ON matches.home_team_id = team.id
    WHERE league_id = 94
        AND season_id = 82
        AND confirmed IS NOT NULL
    GROUP BY home_team_id
UNION
    SELECT team.name, 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
    INNER JOIN team ON matches.away_team_id = team.id
    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;

If it helps, my database schema:

I'm stuck! Hope you can help.

Answer 1

I would compute the home and away results for each team, and then do the aggregation. The following query should do what you need. You just need to join the results with the teams table in order to get the team name. The syntax is working for PostgreSQL, may be you will need to change something for mysql.

select team_id, count(*) as P, sum(W) as W, sum(D) as D, sum(L) as L, sum(GF) as GF, sum(GA) as GA, sum(GD) as GD, sum(PTS) as PTS from (
select home_team_id as team_id,       
       case when home_score > away_score then 1
            else 0 end as W,
       case when home_score = away_score then 1
            else 0 end as D,
       case when home_score < away_score then 1
            else 0 end as L,
       home_score as GF,
       away_score as GA,
       home_score-away_score as GD,
       case when home_score > away_score then 3
            when home_score = away_score then 1 
            else 0 end as PTS            
from matches
where league_id = 94 and season_id = 82 and confirmed is not null
union all
select away_team_id as team_id,       
       case when home_score < away_score then 1
            else 0 end as W,
       case when home_score = away_score then 1
            else 0 end as D,
       case when home_score > away_score then 1
            else 0 end as L,
       away_score as GF,
       home_score as GA,
       away_score-home_score as GD,
       case when home_score < away_score then 3
            when home_score = away_score then 1 
            else 0 end as PTS            
from matches
where league_id = 94 and season_id = 82 and confirmed is not null
) as results
group by team_id
order by pts DESC,gd DESC,gf DESC;

By the way, I don't think that storing the results in a table and updating them after each match is a bad idea. Recomputing always the same results is something that should be avoided, especially if there are many users that are interested in consulting the rank.