CODEWITHSUNDEEP
sqlpostgresql
spotty23544332
spotty23544332

Reputation: 31

psql filter rows on an aggregate function

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

Upvotes: 2

Views: 406

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

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');

Upvotes: 1

Pankaj
Pankaj

Reputation: 2746

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.

Upvotes: 0

Related Questions