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

I don't understand what you're trying to do. Can you give an example table structure, some example data and the expected result for that data? — Mark Byers, Dec 06 '09 at 04:45

score 62 · Accepted Answer · edited May 11 '15 at 19:52

62

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.

edited May 11 '15 at 19:52

Joshua Pinter

45,245
23
243
245

answered Dec 06 '09 at 04:47

OMG Ponies

325,700
82
523
502

3

Congrats on reaching 160K+ rep!! – Joshua Pinter May 11 '15 at 19:52
1

Congrats on reaching 201k+ rep! – manlikeangus Nov 16 '16 at 03:10
Congrats on reaching 224k+ rep! – Infated Sep 27 '17 at 08:00
Congrats on reaching 265k+ rep! – consigliere Jun 19 '19 at 00:13
Congrats on reaching 300k+ rep! – John T May 21 '21 at 22:01

score 0 · Answer 2 · edited Apr 05 '22 at 22:41

0

Simple solution below.

Query:

SELECT  AVG(Column_name) 
FROM  (SELECT Column_name 
FROM  Table
WHERE  ColumnID < number[Limit you want] ) alias

edited Apr 05 '22 at 22:41

John

7,507
3
52
52

answered Dec 20 '17 at 12:02

Aman Dhadwal

9
3

MySQL said: Documentation #1248 - Every derived table must have its own alias – John T May 21 '21 at 23:01

Select average from MySQL table with LIMIT

2 Answers2

Linked