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

Get Help SQL
,
#1

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!

#2

Did you check out the hint in the instructions?

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

#3

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?

#4

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?

#5

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?

#6

Hello @henrylin03 ,

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

1 Like