The Best of Baseball Awards - Returning Only Max/Min Values

Hi everyone!

Currently working on the ‘The Best of Baseball Awards’ Off Platform Project.

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:

baseball solutions 3.1

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.

Thanks in advance for your help!

Did you check out the hint in the instructions?

Try using GROUP BY, AVG and then use ORDER BY DESC

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?

Yes, I believe you could do something like (I don’t know the column names in the project b/c I’ve not done it):

SELECT weight, team, yearid, 
 AVERAGE(weight) OVER( PARTITION BY team ORDER BY yearid) AS Avg_Weight

FROM Table_Name

What would you write for this?

Hi Lisa, I wrote the following:

-- HEAVIEST HITTERS
SELECT 
	AVG(people.weight) OVER (
  	PARTITION BY teams.name
    ORDER BY batting.yearid
  ) AS avg_weight,
	teams.name, 
	batting.yearid 
FROM people 
INNER JOIN batting 
	ON people.playerid = batting.playerid
INNER JOIN teams
	ON batting.team_id = teams.id
GROUP BY 2, 3
ORDER BY
	AVG(people.weight) 
	DESC;

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?

Hello @henrylin03 ,

Can you try adding the column alias 1 to the GROUP BY clause?

1 Like