MySQL AVG ... LIMIT returns total AVG

Question

Table :

a | b
1 | 15
2 | 10
3 | 20
4 | 30

Query:

SELECT AVG(table.b) FROM table ORDER BY table.a ASC LIMIT 3

Will return 18.75 instead of expected 15.

How can I change the query to get my expected result (AVG value for a limited amount of rows)?

Answer 1

You need to use subquery:

SELECT AVG(b) 
FROM (SELECT b
      FROM table 
      ORDER BY table.a ASC 
      LIMIT 3) sub

EDIT:

Without subquery the order of execution is like:

1. FROM
2. AVG (AVG is calculated using all values)
3. ORDER BY (but there is only one value)
4. LIMIT (LIMIT 3 on one value do nothing)

With subquery the order of execution is like:

1. FROM
2. ORDER BY
3. LIMIT (only 3 values)
4. outer query AVG (average is calculated using only 3 values)

More info: Logical query processing (TOP/OFFSET FETCH is the same as LIMIT).

Answer 2

Try this instead:

SELECT AVG(A.b) FROM
(SELECT `table`.b FROM `table` ORDER BY `table`.a ASC LIMIT 3) A;

DEMO