Question 114600 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search