CODEWITHSUNDEEP
sqlsyntax-erroraggregate-functions
karinsofiapaulina
karinsofiapaulina

Reputation: 315

Average of max - SQL

I'm trying to get the average score, of the most up-voted answer, for each StackOverflow question which does not have an accepted answer.

Using the following query:

SELECT AVG(MaxScore) 
FROM
    (SELECT MAX(a.Score) as MaxScore
     FROM Posts a
     JOIN Posts q ON q.Id = a.ParentID
     WHERE q.PostTypeId = 1 AND q.AcceptedAnswerId IS NULL
     GROUP BY q.Id);

only gives me: "Incorrect syntax near ';'"

Any ideas why? Thanks!

Upvotes: 1

Views: 99

Answers (1)

user743382
user743382

Reputation: 

SELECT AVG(MaxScore) 
FROM
    (SELECT MAX(a.Score) as MaxScore
     FROM Posts a
     JOIN Posts q ON q.Id = a.ParentID
     WHERE q.PostTypeId = 1 AND q.AcceptedAnswerId IS NULL
     GROUP BY q.Id) AS sub;

A name for a subquery like this may be required, depending on the database system, even if you don't use it.

Upvotes: 4

Related Questions