CODEWITHSUNDEEP
sql
Shamoon
Shamoon

Reputation: 43501

How can I get the average of the top N rows of a SQL query?

I'm trying

SELECT AVG(q."level")
FROM "Questions" q, "QuestionScores" qs
WHERE qs."QuestionId" = q."id"
AND qs."StudentId"=13
AND q."TopicId" = 45
AND qs."version" = 8
ORDER BY qs."score" DESC
LIMIT 3

But it says that I need to have qs."score" in a GROUP BY or be used in an aggregate function. All I want is to have the top 3 question levels (ranked by score) averaged.

Upvotes: 1

Views: 162

Answers (1)

Bohemian
Bohemian

Reputation: 425033

You need to get the top n row first using a subquery, then get the average:

SELECT AVG(level) FROM (
    SELECT q.level
    FROM Questions q, QuestionScores qs
    WHERE qs.QuestionId = q.id
    AND qs.StudentId=13
    AND q.TopicId = 45
    AND qs.version = 8
    ORDER BY qs.score DESC
    LIMIT 3
) x

I recommend you use the modern (over 25 years old) join syntax:

   SELECT AVG(level) FROM (
    SELECT q.level
    FROM Questions q
    JOIN QuestionScores qs ON qs.QuestionId = q.id
    WHERE qs.StudentId=13
    AND q.TopicId = 45
    AND qs.version = 8
    ORDER BY qs.score DESC
    LIMIT 3
) x

Upvotes: 3

Related Questions