When combining multiple queries into one how do you sum columns together

Question

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

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

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

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
ORDER BY POINTS DESC;

enter image description here

And Away League Standigns using this query:

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

enter image description here

This should be the expected result:

enter image description here

Any idea on what I am doing wrong? Thanks!

Update 1:

Trying out Dans query doesn't work:

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;

I get this result:

enter image description here

Your expected result includes fields that are not being selected. — Dan Bracuk, May 04 '14 at 03:20
@DanBracuk When looking at the expected result please only take into consideration "P W ... PTS" part. This picture is from an old way I was using where I have a LeagueStanding table and updated the values after every match with PHP, but new features I am planning has forced me to use a database query instead. — Jonathan, May 04 '14 at 03:24
Give me a moment and I'll update the query to show names too. — Jonathan, May 04 '14 at 03:29

score 1 · Answer 1 · answered Jun 23 '14 at 11:25

1

You might wanna use a JOIN instead of a UNION. You are using a GROUP BY on the team_id after using the UNION on the subqueries that both have a team_id. That is not going to work... If you just use the join, you can even leave out the group by.

answered Jun 23 '14 at 11:25

Flip Vernooij

889
6
15

score 0 · Answer 2 · answered May 04 '14 at 03:45

I would use this sort of approach. I'm just going to do the points. The logic is the same for the other stuff you want.

select TeamName, 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
when away.home_score = away.away_score then 1 else 0 end) AwayPoints
from team join matches home on team.team_id = home.home_team_id
join matches away on team.team_id = away.away_team_id
where blah blah blah
group by team_id
) temp on team.team_id = temp.team_id
order by points desc

Tried out this query but it's giving me way too many points (e.g. 264 for team in first position) — Jonathan, May 05 '14 at 14:54

score 0 · Answer 3 · answered May 04 '14 at 06:18

0

I think I know what's going on, but don't have a MySQL install to test it.

When you do a group by query, every column that isn't part of the group by clause must be an aggregate function (SUM, MAX, etc). Most DB engines will give you an error if you don't so this; MySQL tries to be helpful? by picking a random row instead.

tl;dr Your outer select needs a bunch of SUMs rather than just selecting *.

answered May 04 '14 at 06:18

James Mason

4,246
1
21
26

It picks whatever value it encounters first on disk. – Chris Wesseling May 04 '14 at 07:37

score 0 · Answer 4 · answered Aug 25 '14 at 16:10

Try this:

SELECT team.name, 
       team_id AS team_id,
       COUNT(*) AS played,
       SUM((CASE WHEN team_score > other_team_score THEN 1 ELSE 0 END)) AS won,
       SUM((CASE WHEN team_score < other_team_score THEN 1 ELSE 0 END)) AS lost,
       SUM((CASE WHEN team_score = other_team_score THEN 1 ELSE 0 END)) AS drawn,
       SUM(team_score) AS goalsFor,
       SUM(other_team_score) AS goalsAgainst,
       SUM(team_score - other_team_score) AS goalDifference,
       SUM((CASE WHEN team_score > other_team_score THEN 3 
                 WHEN team_score = other_team_score THEN 1 
                 ELSE 0 END)) AS points
FROM
    (
        -- LIST TEAM STATS WHEN PLAYED AS HOME_TEAM
        SELECT 
             id                               
             league_id
             season_id
             home_team_id as team_id,
             home_score   as team_score,
             away_score   as other_team_score, 
             confirmed 
        FROM    matches
        UNION ALL
        -- LIST TEAM STATS WHEN PLAYED AS AWAY_TEAM
        SELECT 
             id                               
             league_id
             season_id
             away_team_id as team_id,
             away_score   as team_score,
             home_score   as other_team_score, 
             confirmed 
        FROM matches
    ) matches
INNER JOIN team ON matches.team_id = team.id
WHERE league_id = 94
    AND season_id = 82
    AND confirmed IS NOT NULL
GROUP BY team.name, team_id
ORDER BY POINTS DESC;

When combining multiple queries into one how do you sum columns together

4 Answers4