I have the following columns:

code  note
1      10
2      8
1      9
3      5
3      4

How can I get the code that has the max average note?

My output should be code 1 since the AVGs are 9,5 (code 1), 8 (code 2), 4,5 (code 3)

My query in athena:

select  code from table group by code, avg(note) ORDER BY DESC limit 1

The output error:

GROUP BY clause cannot contain aggregations, window functions or grouping operations

CodePudding user response：

Your grouping function must be in your SELECT list of column, not in your GROUP BY clause.

Try this instead:

select code, avg(note) as avg_of_note
from table 
group by code
order code desc
limit 1

Also: I wouldn't personally name a column code. I could be concerned it would be a reserved word in some SQL dialects.