CODEWITHSUNDEEP
mysqlsql
Purus
Purus

Reputation: 5799

SQL for Game Scores & Prediction System

I am building a Hockey Sports score and prediction system using PHP/MySQL. Below are the system design.

I have a GAMES table where two team numbers and their score in the game is present.The columns from this table are as below.

ID ---- TEAM1 ---- SCORE1 ---- TEAM2 ---- SCORE2
1       70         1           73         2
2       74         0           70         1
3       74         0           73         0

I also have a PICKS table where the details related to user's game predictions are present. Users can guess which team will win in a game and that data is stored in this table. The columns from this table are as below. Each user can guess only once for each game.

ID ---- GAME ---- USER ---- TEAM ---- POINT
1       1         1         70        1
2       2         1         70        1
3       3         1         73        1
3       1         2         70        1

Based on the above available data, I am trying to build up the result where each user (column USER) should be awarded the points(column POINT) for each correct guess. The guess can be validated based on the scores from GAMES table. The final output should be like as below.

USER ---- POINTS ---- CORRECT GUESS COUNT ---- WRONG GUESS COUNT
1         1           1                        2
2         0           0                        1

The columns "CORRECT GUESS COUNT" and "WRONG GUESS COUNT" represent the total number of correct guess and wrong guess done by the user.

I have created a SQL Fiddle for the above tables with some sample data.

http://sqlfiddle.com/#!2/8d469/4/0

EDIT:

Upvotes: 0

Views: 1316

Answers (2)

Stephen
Stephen

Reputation: 103

You'll have to forgive me, if there is a more MySQL way to do it than this (background is Oracle/SQL Server): 

SELECT
  p.user
  ,sum(CASE
     WHEN p.team = g.winner THEN point ELSE 0 END) points
,sum(CASE
     WHEN p.team = g.winner THEN 1 ELSE 0 END) good_guess
,sum(CASE
     WHEN p.team <> g.winner THEN 1 ELSE 0 END) bad_guess
FROM
  picks p
INNER JOIN (
  SELECT
    id game_id
    ,CASE
       WHEN score1 > score2 THEN team1
       WHEN score2 > score1 THEN team2
       ELSE -1 --no team_id as negative
    END winner
    FROM
      games
) g
ON
  g.game_id = p.game
GROUP BY
  p.user

Upvotes: 0

PM 77-1
PM 77-1

Reputation: 13334

SELECT p.user, 
       SUM(IF(g.id IS NOT NULL, p.point, 0)) As points, 
       SUM(IF(g.id IS NOT NULL, 1, 0)) Correct,
       SUM(IF(g.id IS NULL, 1, 0)) Wrong

FROM Games g 
RIGHT JOIN Picks p ON g.id = p.game AND 
          p.team = IF(g.score1 > g.score2 , g.team1, IF(g.score1 < g.score2, g.team2, NULL))
GROUP BY p.user;

SQL Fiddle (with your data)

Upvotes: 1

Related Questions