1

I'm running a fantasy game where the ratings for each player are calculated in the following way:

  • Each round (gameweek) a player plays zero, one or more games. The rating for that round is the average rating scored in all games (stored in match_player)
  • The total season points for each player is the sum of all season round ratings.

I'm trying to list the season points for each player with this query:

SELECT
    `player`.`name`,
    COALESCE(
        (
        SELECT SUM(points)
        FROM
            (
            SELECT ROUND(AVG(`match_player`.`points`)) AS `points`
            FROM `match_player`
            JOIN `match` ON `match`.`id` = `match_player`.`matchID`
            JOIN `round` ON `round`.`id` = `match`.`roundID`
            WHERE `match_player`.`playerID` = `player`.`id` 
               AND `round`.`competitionID` = 1
               AND `round`.`season` = 2018 
               AND `match`.`status` = 'finished'
            GROUP BY  `match`.`roundID`
        ) AS roundPoints
    ), 0) AS seasonPoints
FROM `player`

Byt MySQL is throwing this error:

#1054 - Unknown column 'player.id' in 'where clause'

Is there any way to group by round, calculate average rating and the sum all values for each player?

SQL Fiddle: http://sqlfiddle.com/#!9/bebd2/1


Alternative approach:

SELECT
        `player`.`name`,
        COALESCE(
           SUM(
                (
                  SELECT ROUND(AVG(`match_player`.`points`)) AS `points`
                FROM `match_player`
                JOIN `match` ON `match`.`id` = `match_player`.`matchID`
                JOIN `round` ON `round`.`id` = `match`.`roundID`
                WHERE `match_player`.`playerID` = `player`.`id` 
                   AND `round`.`competitionID` = 1 
                   AND `round`.`season` = 2018 
                   AND `match`.`status` = 'finished'
                GROUP BY  `match`.`roundID`
              )
        ), 0) AS seasonPoints
    FROM `player`

This time, the error returned is:

Subquery returns more than 1 row

http://sqlfiddle.com/#!9/bebd2/2

  • The rating for that round is the average rating scored in all games What is a rating value for a round if zero games were played? SQL Fiddle: Show the reference answer for these source data. – Akina Oct 10 '18 at 4:51
  • Zero games played = 0 points, that's way I'm using COALESCE() – Javier Marín Oct 10 '18 at 9:03
  • I've updated the SQL Fiddle with the reference answer – Javier Marín Oct 10 '18 at 9:12
1

I've updated the SQL Fiddle with the reference answer

1) Referenced answer seems to have a misprint: -- Player 1 (Round 1: 5pts ((10+5)/2), Round 2: 6pts), whereas (10+5)/2 = 7.5 (or 8 while rounding to integer).

2) Look at

SELECT `player`.`name`, `match`.`roundID`, ROUND(AVG(`match_player`.`points`)) AS `points`
FROM player
JOIN `match_player` ON `match_player`.`playerID` = `player`.`id`
JOIN `match` ON `match`.`id` = `match_player`.`matchID`
JOIN `round` ON `round`.`id` = `match`.`roundID`
WHERE `round`.`competitionID` = 1
  AND `round`.`season` = 2018
  AND `match`.`status` = 'finished'
GROUP BY `player`.`name`, `match`.`roundID`
;

It fits your reference answer.

  • You're right, there was a mistake in the referenced answers, I've fixed them. – Javier Marín Oct 10 '18 at 9:25
  • Regarding your solution, I'm trying to list for each player their season points, which are the sum of all round points – Javier Marín Oct 10 '18 at 9:26
  • @JavierMarín Wrap my query like SELECT name, SUM(points) FROM (my_query_text) AS subquery GROUP BY name. – Akina Oct 10 '18 at 9:27
-1

Change 'player'.id to 'match'.id like this.

SELECT
    `player`.`name`,
    COALESCE(
        (
        SELECT SUM(points)
        FROM
            (
            SELECT ROUND(AVG(`match_player`.`points`)) AS `points`
            FROM `match_player`
            JOIN `match` ON `match`.`id` = `match_player`.`matchID`
            JOIN `round` ON `round`.`id` = `match`.`roundID`
            WHERE `match_player`.`playerID` = `match`.`id` 
               AND `round`.`competitionID` = 1 
               AND `round`.`season` = 2018 
               AND `match`.`status` = 'finished'
            GROUP BY  `match`.`roundID`
        ) AS roundPoints
    ), 0) AS seasonPoints
FROM `player`

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.