How to use aggregate function using LIMIT?

Question

Is it possible to SUM or AVG the top 10 results? I have a list of several thousand values. I want to know the average of the top 10 values. I tried this:

SELECT AVG(some_val)
FROM table
ORDER BY some_val DESC
LIMIT 10;

But this gives me the average of the entire list of values from the table, not just the top 10.

I'm using MySQL. I'd like to write this as one SQL statement.

mellamokb · Accepted Answer · 2012-11-19 15:51:24Z

9

You need to select the top 10 rows first, then average them.

SELECT AVG(some_val)
  FROM (SELECT * FROM table ORDER BY some_val DESC LIMIT 10) t;

Otherwise, you are selecting the average of all the rows, and then returning up to 10 results (because LIMIT applies last). As you probably discovered, you only get a single row back (the average), and the LIMIT has no effect.

answered Nov 19, 2012 at 15:51

mellamokb

56.8k12 gold badges111 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use aggregate function using LIMIT?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related