MySQL SUM multiple columns

Question

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.

Answer 1

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