Menu
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.
947
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).
An aggregate function performs a calculation on a set of values, and returns a single value. Except for COUNT(*) , aggregate functions ignore null values. Aggregate functions are often used with the GROUP BY clause of the SELECT statement.
For example, avg() (average) can be computed by sum()/count(), where both sum() and count() are distributive aggregate functions. Similarly, it can be shown that min_N() and max_N() (which find the N minimum and N maximum values, respectively, in a given set) and standard_deviation() are algebraic aggregate functions.
There are five aggregate functions, which are: MIN, MAX, COUNT, SUM, and AVG.
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.
152
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
