0

I'm working on a soccer database right now, and I am trying to solve one little problem.

So I have the table games, where I stored home team, away team, miscstats and boxscore:

game_id | hometeam_id | awayteam_id | miscstats_id | boxscore_id
1         2             4             1              1
2         3             7             2              2
3         4             3             3              3
4         8             5             4              4

In boxscores I store one row per player and match:

boxscore_id | player_id | team_id | goals | fouls
1             1           2         1       4
1             2           2         0       3
1             3           4         1       7
1             4           4         1       10
2             5           3         5       0

And in miscstats I store random stats, like number of yellow cards showed to the bench players:

miscstats_id | homebenchyellowcards | awaybenchyellowcards
1              3                      4
2              1                      5
3              2                      6
4              7                      10

Now, for some advanced statistics calculation, I would like to aggregate the number of homebenchyellowcards and awaybenchyellowcards for each team. So, for example, if my select function is correct, team number 3 has 7 bench yellow cards, 1 from game 2 (when team played at home) and 6 from game 3 (when team played away).

I am trying to do this:

SELECT (IF (e.hometeam_id=b.team_id, x.homebenchyellowcards, x.awaybenchyellowcards)) as 
bench_yellow_cards FROM miscstats as x INNER JOIN games as e ON (e.miscstats_id=x.miscstats_id)
INNER JOIN boxscores as b ON (e.boxscore_id=b.boxscore_id) WHERE b.team_id = 2

But it gives me '1' as a result. I have been reading that this is because the 'if' function just analyses the first row when this condition is met (so, game_id 2) and then stops with the rest of the rows. I tried SUM statements too, but then it sums every row in homebenchyellowcards.

Anybody has an idea on how passing the if condition to all the rows in order to sum them properly?

Thanks in advance!

0

Your description looks messy. Expression from your SELECT statement will be evaluated for each row in received result set. I didn't understand why do you need to join boxscores table. Actually your original query should return more than one row. If you want to find the number of yellowcards for team_id = 2 you can use such query:

SELECT SUM(IF(e.hometeam_id = 2, x.homebenchyellowcards, x.awaybenchyellowcards))
FROM games AS e
JOIN miscstats AS x ON e.miscstats_id = x.miscstats_id 
WHERE e.hometeam_id = 2 OR e.awayteam_id = 2;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.