# Using an IF statement for all rows in a query

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?

``````SELECT SUM(IF(e.hometeam_id = 2, x.homebenchyellowcards, x.awaybenchyellowcards))