MySQL unknown column in subquery

MySQLsubquery

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

Best Answer

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.

Related Question