CODEWITHSUNDEEP
mysqlsqlmaxaverage
Oleg Sv
Oleg Sv

Reputation: 43

max average value in MySql

How to get the maximum from averages values in MySQL? The following query returns average values of amounts from table orders grouped by customers.

SELECT AVG(amount)
FROM orders
GROUP BY cust;

I want to receive a maximum value from average values using a single query with aggregate functions. Using ORDER BY ... DESC LIMIT 1 surely works, but what I am interested in is getting the maximum average value using aggregate functions solely. Is it possible at all? Thanks

Upvotes: 2

Views: 9056

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

I would do this with order by and limit:

SELECT AVG(o.amount) as avg_value
FROM orders o
GROUP BY cust
ORDER BY avg_value DESC
LIMIT 1;

This allows you to get the cust for the maximum as well.

Upvotes: 3

juergen d
juergen d

Reputation: 204924

select max(avg_value)
from 
(
  SELECT AVG(amount) avg_value FROM orders GROUP BY cust
) tmp

Upvotes: 6

Related Questions