Take the 2-minute tour ×

Here's an example table:

|   home_team   |   away_team   |   game_outcome   |
|    Chelsea    |    Arsenal    |     Home wins    |
|    Everton    |   Liverpool   |     Away wins    |
|    Arsenal    |    Chelsea    |     Home wins    |

What I need is the number of times 'Chelsea' appears WHERE game_outcome = 'Home wins' which is 2 in this example.

Normally this would be very easy but I'm stuck because I have to search 2 columns. I really need some insight.

share|improve this question
As Andrew answered already (correct imho :)) I am still wondering why you want to count in your example 2 times Chelsea and home wins, because in this case Chelsea win once and lost once. –  YvesR Apr 20 '12 at 13:14
Or perhaps what you're after is: SELECT * FROM games_table g WHERE (home_team='Chelsea' AND game_outcome='Home wins') OR (away_team='Chelsea' AND game_outcome='Away wins') –  YS. Apr 20 '12 at 13:17
@YvesR Good question. This was a simplified example but really I'm counting the number of correct predictions of the game outcome for team Chelsea, given the prediction is 'Home wins'. :) –  Alternatex Apr 20 '12 at 13:18
Mark Andrews answer as accepted then :) –  YvesR Apr 20 '12 at 13:24
add comment

1 Answer

up vote 3 down vote accepted
FROM   games_table
WHERE  game_outcome = 'Home wins'
AND    (home_team = 'Chelsea' OR away_team = 'Chelsea')
share|improve this answer
Thank you. Simple and elegant. –  Alternatex Apr 20 '12 at 13:16
AND 'Chelsea' IN ( home_team , away_team ) –  onedaywhen Apr 20 '12 at 14:21
add comment

Your Answer


