I am building a sports system in PHP/MySQL. With some help on StackOverFlow, I have built it.

I have OW\_SPORTS\_GAMES table which has all details about each games played with team Id and scores. Here is the structure.

![enter image description here](https://i.stack.imgur.com/pl0tc.png)

Also there is another table OW\_SPORTS\_PREDICTIONS where user predictions about games results are stored. User can predict which team will be winning the game. Here is the table structure.

![enter image description here](https://i.stack.imgur.com/RpgzW.png)

I am looking to get the result where I can know how many correct and wrong predictions each user has made. If it's a correct guess, user is awarded the points present for each game (points column). The output should ignore any games where scores of both team is 0.

Here is my current SQL:

```
SELECT p.userId,
       SUM(IF(g.id IS NOT NULL, 1, 0)) correct,
       SUM(IF(g.id IS NULL, 1, 0)) wrong,
       SUM(IF(g.id IS NOT NULL, g.points, 0)) AS points
  FROM
       (SELECT * FROM ow_sports_games WHERE seasonId = 10 AND (homeTeamScore > 0 OR awayTeamScore > 0) ) g
  RIGHT JOIN ow_sports_predictions p
     ON g.id = p.gameId
    AND p.teamId = IF(g.homeTeamScore > g.awayTeamScore , g.homeTeam, IF(g.homeTeamScore < g.awayTeamScore , g.awayTeam, NULL))
  GROUP BY p.userId ORDER BY points DESC, correct DESC, wrong DESC;

```

With this SQL I am getting wrong stats where games not predicted by user and scores 0-0 are also considered.

**SQL Fiddle: <http://sqlfiddle.com/#!2/f4c9ed/2>**

In the same fiddle data, it should be 2 correct and 2 wrong predictions. But it comes as 2 correct and 4 wrong predictions.