SUM rows FROM a table only if one row have some match criteria with the rest of the rows

Posted on

Question :

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!

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

One solution is this

SELECT 
    MIN(`id_player`) id_player,SUM(`goals`),SUM(`fouls`)
FROm
    boxscore
WHERE `id_team` = (SELECT `id_team` FROM boxscore WHERE `id_player` = 1 LIMIT 1)
GROUP BY `id_boxscore`
HAVING id_player = 1

REsult is

id_player | SUM(`goals`) | SUM(`fouls`)
--------: | -----------: | -----------:
        1 |            4 |            8

db<>fiddle here

Leave a Reply

Your email address will not be published.