CODEWITHSUNDEEP
mysqlsql
RGriffiths
RGriffiths

Reputation: 5970

SQL query to compare individual average with a group average

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

Upvotes: 4

Views: 4613

Answers (1)

phadaphunk
phadaphunk

Reputation: 13303

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)));

Upvotes: 1

Related Questions