CODEWITHSUNDEEP
phpmysqlsqldatabase
Pol
Pol

Reputation: 1341

Sql request to find the MAX AVG

I would like to get the max avg of data group by a column. I have a database built like this:

id | late | from 
---|------|--------
 1 |  13  |a
 2 |   3  |c
 3 |   2  |c
 4 |  27  |a
 5 |   7  |b
 6 |   9  |b

With that, I want to find the "from" who have the max avg of "late". Here it has to return"a":

 AVG(a) = ((13 + 27) / 2) = 20
 AVG(b) = ((7 + 9) / 2) = 8
 AVG(c) = ((3 + 2) / 2) = 2.5

I try something like this :

SELECT id FROM Record HAVING MAX(AVG(late)) GROUP BY from

But it doesn't work...

Thank you for tour help !

Upvotes: 1

Views: 74

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a limit 1 on order by avg in descending order:

select `from`
from my_table 
group by `from` 
order by avg(late) desc 
limit 1

But you should not use a column with a name such as from; not without escaping it that is, since it is a MySQL reserved word.

Upvotes: 2

Related Questions