AVG(MAX(values))

[ross1228]

I know AVG(MAX(values)) does not work, but I need something to do the same.

I have a quiz that has different responses and each response is scored differently. I need to get a list of max possible scores for each question and then find the average of that list.
Is this possible to do in a single query?

example
?_id, answer_id, score
1, 1, 0
1, 2, 1
1, 3, 2
2, 4, 0
2, 5, 1
2, 6, 2
3, 7, 0
3, 8, 5
3, 9, 10

SELECT MAX(score) FROM scores GROUP BY ?_id
So max values will be 2, 2, 10
And now I need to get the average of that

 

[ross1228]

I figured it out..
SELECT AVG((SELECT MAX(score) FROM scores WHERE question_id = ?_id)) FROM scores GROUP BY ?_id