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

Question

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

You typically GROUP BY the same columns as you SELECT, except those who are arguments to set functions. — jarlh, Apr 25 '23 at 12:44
Which dbms are you using? GROUP BY isn't expected to chose randomly. — jarlh, Apr 25 '23 at 12:45
Please tag your question with the DBMS you are using. Most DBMSs would error with the SQL you've provided (as team is neither aggregated or in the group by) — NickW, Apr 25 '23 at 12:45
I am using phpmyadmin. I cannot group by team as this will yield 2 result rows for my example instead of 1. — jk31, Apr 25 '23 at 13:06
Does this answer your question? [Get records with max value for each group of grouped SQL results](https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results) — nbk, Apr 25 '23 at 13:12

score 0 · Answer 1 · answered Apr 25 '23 at 13:14

0

You can use window functions to rank the teams of each player on a given season, then aggregation:

select playerid, 
    max(case when rn = 1 then team end) latest_team, 
    season, 
    sum(points) total_points
from (
    select g.*, 
        row_number() over(partition by playerid, season order by date desc) rn
    from gamestats_players g
) g
group by playerid, season

answered Apr 25 '23 at 13:14

GMB

216,147
25
84
135

1

It does, https://dbfiddle.uk/uvwH0iVq – jarlh Apr 25 '23 at 13:45
1

I have not downvoted anything. Have not tested it yet, will do later/tomorrow. Thanks for the input! – jk31 Apr 25 '23 at 14:08

score 0 · Accepted Answer · edited Apr 26 '23 at 12:29

0

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

edited Apr 26 '23 at 12:29

jk31

15
5

answered Apr 25 '23 at 13:36

SelVazi

10,028
2
13
29

Thanks. It basically works BUT for some players it will yield multiple results and I do not fully understand why. – jk31 Apr 26 '23 at 11:55
It could be with different season ? Can you update this fiddle https://dbfiddle.uk/eaFX-oJ8 with some simple data that provide duplicates then share it here as comment – SelVazi Apr 26 '23 at 12:01
Found it out: The problem arises if there are multiple game entries for one player on a date that matches the "latestDate". I updated your fiddle with one more game on the same date as the last one. – jk31 Apr 26 '23 at 12:09
1

Switching to SELECT DISTINCT solves the problem. – jk31 Apr 26 '23 at 12:25
It can solve the problem only if the team is the same, try using my solution based on `group by` with `min` or `max` to pick only one team https://dbfiddle.uk/hxd_GIE1 – SelVazi Apr 26 '23 at 12:29

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

2 Answers2