MySQL SUM Rows – Sum Rows Only if One Row Matches Criteria

MySQL

Sorry if the title is a little bit odd, but I am struggling even with explaining myself on this.

I am trying to create a soccer stats database. Right now I have a 'boxscore' table like this:

id_boxscore  id_player  id_team  goals  fouls
-----------  ---------  -------  -----  -----
1            1          1        1      3
1            2          1        3      5
1            3          2        2      8
2            4          5        0      6
2            4          5        0      6
3            2          1        5      9
3            3          1        11     4

So, for some advance data calculations, I need to know the accumulated totals from the team from games where the player participated. The good query should give me something like this if, for example, I am selecting accumulated stats from id_player = 1:

id_player  accGoals   accFouls
---------  ---------  --------
1          4          8

Right now my query is looking like this:

SELECT SUM(b.goals) FROM boxscore as b, (SELECT b.id_boxscore from boxscore as b 
WHERE b.id_player = 1) AS c WHERE b.id_boxscore=c.id_boxscore

But is giving me the accumulated stats from games where the player with id 1 has played (both from his team and from the opponent):

id_player  accGoals   accFouls
---------  ---------  --------
1          6          22

Any ideas? I am sure this is really simple but I am in a dead-end right now.

Thanks in advance!

Best Answer

Is this close to what you expect?

SELECT a.id_player, SUM(b.goals) as accGoals, SUM(b.fouls) as accFouls
FROM boxscore as a
JOIN boxscore as b
    USING(id_team, id_boxscore)
WHERE a.id_player = 1
GROUP BY a.id_player
;

I.e. sum all goals, fouls from boxscore, where id_team and id_boxscore is the same as player 1

Related Question