SQL query to compare individual average with a group average

Question

I have a simple table with names and a score. What I would like to do is produce a list of names with their average score, where their average is greater than the average of the group.

I have the following SQL statement that works in Access but not my server (running MySql):

SELECT Person, Avg(Score) AS PersonAverage FROM TblScores GROUP BY Person HAVING (((Avg(Score))>(SELECT Avg(Score) AS AverageOfAllScores FROM TblScores;))); 

the server is saying that the syntax is wrong but I can't see where. Any ideas? Thanks

Answer 1

You don't need the first ; since it is a statement terminator.
Change this :

SELECT Person, Avg(Score) AS PersonAverage FROM TblScores GROUP BY Person HAVING (((Avg(Score))>(SELECT Avg(Score) AS AverageOfAllScores FROM TblScores;)));

with this :

 SELECT Person, Avg(Score) AS PersonAverage FROM TblScores GROUP BY Person HAVING (((Avg(Score))>(SELECT Avg(Score) AS AverageOfAllScores FROM TblScores)));