psql filter rows on an aggregate function-CodePudding

I have the following table:

 ------ ----------- ------------- -------------- 
| year | team_name | player_name | total_points |
 ------ ----------- ------------- -------------- 
| 1992 | Magic     | Shaq        | 2000         |
 ------ ----------- ------------- -------------- 
| 1992 | Magic     | Steve Kerr  | 4000         |
 ------ ----------- ------------- -------------- 
| 1996 | Lakers    | Shaq        | 2300         |
 ------ ----------- ------------- -------------- 
| 1996 | Lakers    | Magic       | 1000         |
 ------ ----------- ------------- -------------- 
| 2004 | Heat      | Shaq        | 3000         |
 ------ ----------- ------------- --------------

I am trying to write a query to find all teams where Shaq scored the most amount of points.

Here is my attempt:

SELECT year, team, MAX(total_points) FILTER(WHERE player_name = 'Shaq'
FROM basketball
GROUP BY year, team;

This is obviously not correct. I think I am using FILTER incorrectly because I cant get the teams where Shaq scored the most points on that team

The correct table should only have the 3rd and last row

CodePudding user response：

You are close. You want a HAVING clause comparing the teams' yearly maximum points with Shaq's points and only keep those that match.

SELECT year, team, MAX(total_points)
FROM basketball
GROUP BY year, team
HAVING MAX(total_points) = MAX(total_points) FILTER (WHERE player_name = 'Shaq');

CodePudding user response：

Query -

select year, team_name,high_score from (
  select *, 
  max(total_points) over (partition by year, team_name) high_score 
  from basketball) t
where t.total_points = t.high_score
and t.player_name='Shaq'

fiddle.