Why don't max function and group by work well together in SQL?-CodePudding

I have created a Match table and want to run this query:

select player_id, max(goals)
from soccer.player_match_stat
group by player_id

The aim is to find the player_id who has the max number of goals in this table. So, I am grouping the tables data by player_id and then getting the max() of goals, but the result isn't correct!

Match:

player_id	goals
1	2
2	5
1	4

Expected Result:

player_id	goals
1	6

I would be thankful if you help me with this problem :)

CodePudding user response：

May be you need aggregate not by max but by sum:

select player_id, sum(goals)
from soccer.player_match_stat
group by player_id
order by 2 desc
limit 1

If you aggregate by max you just get maximal value of goals for player per game. If you aggregate by sum you get total number of goals for player across all the games. As i understood from question, you have to calculate total amount of goals (6 goals for player 1).

CodePudding user response：

What you are doing with your query is to group by player_id and for each grouped player_id you display his/her max(goals). I guess that is what you are experiencing when you say "the result isn't correct" (you should show the table and the result for us to help better by the way).

What you want is

to find the player_id who has the max number of goals in this table.

So one player_id and that player_id is the one who has the highest max(goals) score among all those that you actually have already listed in your mentioned "wrong" query.

Why not using a query to query the result of your query to select the row with the highest max(goals)???

E.g. you could sort your result from highest to lowest and the first one is your pick (think of ordered by, limit and maybe an alias for max(goals), e.g. max(goals) as max_goals).

CodePudding user response：

Try following:

CREATE TABLE Match_tbl(
player_id int,  
goals int             );

INSERT INTO Match_tbl values    
                      (1,2),
                      (2,5),
                      (1,4);

In MySQL or PostgreSQL :

  SELECT player_id
     , total_goals
  FROM (
     SELECT player_id
          , sum(goals) as total_goals
     FROM Match_tbl
     GROUP BY player_id
 ) as t1
  ORDER BY total_goals DESC
 LIMIT 1

https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/180

SQL Server:

 SELECT  TOP 1  player_id
      , sum(goals) as total_goals
 FROM Match_tbl
 GROUP BY player_id
 ORDER BY total_goals DESC

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=614d8063d98d4655fece19b6ac9f0faa

Result:

player_id total_goals
1            6