Hi All,
I’m having some trouble with the “Best of Baseball Awards” lesson in the Design Databases with PostgreSQL course.
Specifically with the first query “Heaviest Hitters” (awarded to the team with the highest average weight of its batters on a given year). Here’s what I’ve got so far:
SELECT years, MAX(avg_weight) as max_weight
FROM (SELECT batting.yearid AS years, teams.name AS team, round(avg(people.weight), 2) AS avg_weight
FROM batting, teams, people
WHERE batting.teamid = teams.teamid
AND batting.playerid = people.playerid
GROUP BY batting.yearid, teams.name) AS weights
GROUP BY weights.years;
This gives me a sorted list of the highest average weight per year (which is what we want!). However, when I try to also list the team name in the following way:
SELECT years, team, MAX(avg_weight) as max_weight
FROM (SELECT batting.yearid AS years, teams.name AS team, round(avg(people.weight), 2) AS avg_weight
FROM batting, teams, people
WHERE batting.teamid = teams.teamid
AND batting.playerid = people.playerid
GROUP BY batting.yearid, teams.name) AS weights
GROUP BY weights.years;
I get the following error:
ERROR: column "weights.team" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select years, team, max(avg_weight) as max_weight
And if I add weights.team
to the GROUP BY
statement, the output changes to be identical to the subquery (because we are now grouping by year
, then by team
, and then taking the max of avg_weight
).
So, my question is: is there some way I can “join” the columns I’ve selected with the team name that corresponds to the maximum average weight each year?
Thanks in advance!
(I’m running PostgreSQL 13.0 via pgAdmin 4 4.27 on macOS Catalina 10.15.7.)