CODEWITHSUNDEEP
sqlamazon-athena
OdiumPura
OdiumPura

Reputation: 631

Get max value by a group of column in SQL/Athena

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

Upvotes: 0

Views: 1859

Answers (1)

peterh
peterh

Reputation: 19285

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 by avg_of_note 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.

Upvotes: 1

Related Questions