Average of an average

Question

I have a table called ranks which stores a 1-7 value.

r_id | r_userid | r_class | r_showcaseid | r_submitterid

r_class is the 1-7 value, and r_showcaseid is the item given the rating.

The following query selects the average rating for the top 5 submissions (r_showcaseid) of a user. I need to get the average of the rows' values from this query (i.e. an average of an average):

SELECT avg(r_class) FROM ranks WHERE r_submitterid=? GROUP BY r_showcaseid ORDER BY avg(r_class) DESC LIMIT 5

So, the value I want is (6+5+1.5)/3 ≈ 4.166...

I have tried avg(avg(r_class)) but this gives me an error. How can I get the average value of avg(r_class) of the rows from the query?

Answer 1

select avg(avgrcls)
from
(
SELECT avg(r_class) as avgrcls
FROM ranks 
WHERE r_submitterid=?
GROUP BY r_showcaseid 
ORDER BY avg(r_class) DESC LIMIT 5
) t

You can do it using a outer query.