CODEWITHSUNDEEP
mysqlsql
eldblz
eldblz

Reputation: 808

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?

Upvotes: 2

Views: 1927

Answers (2)

Nallamachu
Nallamachu

Reputation: 1488

This is not exactly related to the question asked, I am placing this because some one might get benefited.

I got the hackerearth problem to write mysql query to fetch top 10 records based on average of product quantity in stock available.

SELECT productName, avg(quantityInStock) from products 
group by quantityInStock 
order by quantityInStock desc
limit 10

Note: If someone can make better the above query, please welcome to modify.

Upvotes: 0

seahawk
seahawk

Reputation: 1912

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;

Upvotes: 1

Related Questions