SQL: use specific value in GROUP BY for row with multiple values

Following background: I have a database with single game statistics of players in games of our NBA 2k online league. So there is one entry for each player for each game. These single game statistics get put together to show the season average. If a player played for multiple teams in one season, it ideally should show the team he last played for in the season, but with my query it might show the wrong team.

table: gamestats_players rows: playerID, gameID, season, date, team, points (and multiple other statistics)

This the query I use:

SELECT playerID, season, team, sum(pts) 
FROM gamestats_players
GROUP BY playerID, season
ORDER BY date desc

One player has 2 entries with Team A who he played for first and 1 entry for Team B where he was traded to recently. The query result still shows Team A and as far as I understand if there are multiple values for a row that gets consolidated during GROUP BY then just one random value is selected. How can I achieve that during the group by he chooses the latest entry for the shown team and not just any entry?

Database entries:

gamestats_players

gameID	playerID	Team	season	Date	Points
1	1	Team A	36	2023-04-01	10
2	1	Team A	36	2023-04-02	10
3	1	Team B	36	2023-04-03	10

query result

playerID	Team	season	sum(points)
1	Team A*	36	30

*should be Team B

Solution

First we get the latest date using :

SELECT playerID, season, MAX(Date), sum(Points) as total_points
FROM gamestats_players
GROUP BY playerID, season

Result :

playerID    season  latestDate  total_points
1           36      2023-04-03  30

Then we join it with the table using inner join as follows :

select distinct t.playerID, t.Team, t.season, s.total_points
from gamestats_players t
inner join (
  SELECT playerID, season, MAX(Date) as latestDate, sum(Points) as total_points
  FROM gamestats_players
  GROUP BY playerID, season
) as s on s.playerID = t.playerID and s.season = t.season and s.latestDate = t.Date

Result :

playerID    Team    season  total_points
1           Team B  36      30

Demo here