Average value for top n records?

i have this SQL Schema: http://sqlfiddle.com/#!9/eb34d

In particular these are the relevant columns for this question:

ut_id,ob_punti

I need to get the average of the TOP n (where n is 4) values of “ob_punti” for each user (ut_id)

This query returns the AVG of all values of ob_punti grouped by ut_id:

SELECT ut_id, SUM(ob_punti), AVG(ob_punti) as coefficiente 
       FROM vw_obiettivi_2015 
       GROUP BY ut_id ORDER BY ob_punti DESC

But i can’t figure out how to get the AVG for only the TOP 4 values.

Can you please help?

Answer

It will give SUM and AVG of top 4. You may replace 4 by n to get top n.

select ut_id,SUM(ob_punti), AVG(ob_punti) from (
select @rank:=if(@prev_cat=ut_id,@rank+1,1) as rank,ut_id,ob_punti,@prev_cat:=ut_id

from Table1,(select @rank:=0, @prev_cat:="")t
order by ut_id, ob_punti desc
  ) temp
  where temp.rank<=4
group by ut_id;