I have another question about sum.
I'd like to sum the scored of a baseball team, adding the scored when playing as local with the score when playing as visitor.
The matches table is like this:
Baseball_matches (Id, IdTeamHome, IdTeamAway, ScoreHome, ScoreAway, Status)
I'd like to group by Team, showing the total score for each team by adding the scored of matches that have the status os "played"
For each team: SUM ScoreHome when IdTeamHome + SUM ScoreAway when IdTeamAway
How can I do this?
Thanks.
解决方案SELECT Team,
SUM(Score) AS Score,
SUM(Won) AS Won,
SUM(Lost) AS Lost
FROM (SELECT IdTeamHome AS Team,
SUM(ScoreHome) AS Score,
SUM(CASE
WHEN ScoreHome > ScoreAway THEN 1
ELSE 0
END) AS Won,
SUM(CASE
WHEN ScoreHome < ScoreAway THEN 1
ELSE 0
END) AS Lost
FROM matches
WHERE Status = 'Played'
GROUP BY IdTeamHome
UNION ALL
SELECT IdTeamAway AS Team,
SUM(ScoreAway) AS Score,
SUM(CASE
WHEN ScoreHome < ScoreAway THEN 1
ELSE 0
END) AS Won,
SUM(CASE
WHEN ScoreHome > ScoreAway THEN 1
ELSE 0
END) AS Lost
FROM matches
WHERE Status = 'Played'
GROUP BY IdTeamAway) D
GROUP BY Team