I have some data that looks like this:
students:
id score section ...
--------------------------------------------
1 85 A ...
2 40 A ...
3 61 B ...
4 71 B ...
5 80 B ...
I would like to show the score each student along with the maximum score of their section.
students:
id score section section_max_score ...
--------------------------------------------
1 85 A 85 ...
2 40 A 85 ...
3 61 B 80 ...
4 71 B 80 ...
5 80 B 80 ...
I am trying to do something like this:
select id, score, section, max(score) as section_max_score from students group by section;
This gives an error that I need to include other columns in the group by.
select id, score, section, max(score) as section_max_score from students group by section, score, id;
This just shows the individual score of each student instead of the section maximum.
What am I missing and how do I fix it?
Essentially, I need to be able to use a particular column as well an aggregate on that column in a group by query.
You can use the window function max() :
select id, score, section, max(score) over ( partition by section) as section_max_score
from students
Or use an inner join to join your table to a dataset of maximum score per section:
select s.*, t.section_max_score
from students s
inner join (
select section, max(score) as section_max_score
from students
group by section
) as t on s.section = t.section