"Best of Baseball" PostgreSQL Challenge Help Needed!

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.)

I feel your pain. I’ve been struggling all day trying to accomplish the same.
I believe, the Codecademy solutions for this project don’t really answer their own questions correctly. They provide lists of a variable (in this instance: average weight) for each team and year, rather than providing the team with the highest/lowest value for the variable for each individual year.
So hats to you for trying to accomplish a ‘correct’ solution. Have you found a solution, yet?

I would expect to solve it with a Window Function as following:

WITH
	avg_weight 
  AS
  (
    SELECT
        teams.name AS team_name,
        batting.yearid AS season,
        ROUND(AVG(people.weight)) AS avg_player_weight
      FROM 
        people,
        batting,
        teams
      WHERE people.playerid = batting.playerid
      AND teams.id = batting.team_id
      GROUP BY
        batting.yearid,
        teams.name
      ORDER BY avg_player_weight DESC
  )
SELECT
 	team_name,
 	season,
  MAX(avg_player_weight) OVER (PARTITION BY season) AS avg_player_weight
FROM 
	avg_weight
ORDER BY avg_player_weight DESC;

But it doesn’t provide the desired results, as I expect only ONE result row for each year.

team_name season avg_player_weight
team_name season avg_player_weight
Tampa Bay Rays 2009 221
Atlanta Braves 2014 221
Tampa Bay Rays 2014 221
Pittsburgh Pirates 2009 221
Oakland Athletics 2009 221

etc.

It would appear PostgreSQL does not support the MAX() function (as expected) in Window Functions although it does not produce an error: PostgreSQL: Documentation: 13: 3.5. Window Functions.