In the first 2 questions in Part 3 (‘Heaviest Hitters’ and ‘Shortest Sluggers’), the solutions provided a table that lists out heaviest players, grouped by teams and year, in no particular order:
Is there a way to use perhaps Windows Functions to only return the maximum average weight (for the first question, for example), per team, per year? I tried to use MAX(AVG(people.weight)), but it is not allowed in Postbird.
Hi Lisa, that hint provided the above table (screenshotted in my original post), but did not provide, for every year, the maximum avg weight, and name of the team. In the way I am asking, there should only be one yearid, maybe two or three if they are tied 1st.
Is there a way to use a Windows Function to do this?
-- HEAVIEST HITTERS
AVG(people.weight) OVER (
PARTITION BY teams.name
ORDER BY batting.yearid
) AS avg_weight,
INNER JOIN batting
ON people.playerid = batting.playerid
INNER JOIN teams
ON batting.team_id = teams.id
GROUP BY 2, 3
and got the following error:
column “people.weight” must appear in the GROUP BY clause or be used in an aggregate function
Stack Overflow has noted that this is because I need to use a subquery instead. How would I go about doing that?