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.

Martin Smith · Accepted Answer · 2011-10-14 23:15:38Z

2

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

edited Oct 14, 2011 at 23:15

answered Oct 14, 2011 at 22:51

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

John Mathison Over a year ago

Thanks Martin, that's awesome !!! Also, I'd like to retrieve in the same query the number of matches a team has won and lost...is it possible?

John Mathison Over a year ago

Great!!! It looks very complicated. Is it going to work all right for a large table of matches? I'm very worried about temporary tables and all that stuff. Performance is very important for this project !!!

Martin Smith Over a year ago

No idea. Can't see any obvious alternatives based on your table structure anyway.

John Mathison Over a year ago

Well I was trying to SELECT all teams and then making a SELECT SUM for every column I need from matches table...

Collectives™ on Stack Overflow

MySQL SUM multiple columns

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related