Select average from MySQL table with LIMIT

Question

I am trying to get the average of the lowest 5 priced items, grouped by the username attached to them. However, the below query gives the average price for each user (which of course is the price), but I just want one answer returned.

SELECT AVG(price)    FROM table   WHERE price > '0' && item_id = '$id'  GROUP BY username  ORDER BY price ASC     LIMIT 5 

Answer 1

I think this is what you're after:

SELECT AVG(items.price)   FROM (SELECT t.price           FROM TABLE t          WHERE t.price > '0'             AND t.item_id = '$id'       ORDER BY t.price          LIMIT 5) items 

It will return the average of the 5 lowest prices - a single answer.