MySQL unknown column in subquery

Question

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

• Akina: 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.
• Javier Marín: Zero games played = 0 points, that's way I'm using COALESCE()
• Javier Marín: I've updated the SQL Fiddle with the reference answer

Answer 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.

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

Answer 2

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`