Geek asks and answers

Closest to average SQL

I am looking for a query to get the closest value for the average of all values. For example, I have in column A the values ​​are 1, 2, 8 The average value is 3.667. Therefore, I want the query to return 2, as it is closest to the average.

+5
source share
3 answers
SELECT TOP 1 myTable.*
FROM myTable
CROSS JOIN (SELECT AVG(A) AS averageA FROM myTable) AS averageA
ORDER BY ABS(averageA.averageA - myTable.A)

SQL Fiddle

+8
source 
SELECT number, ABS(number - (SELECT AVG(number) FROM numberTable)) AS deviation 
FROM numberTable 
ORDER BY deviation ASC 
LIMIT 1;
+2
source 
SELECT MAX(A)
FROM myTable
WHERE A < (SELECT AVG(A) AS averageA FROM myTable)
0
source

More articles:


All Articles