CODEWITHSUNDEEP
sqlgroup-bymax
yasamingol
yasamingol

Reputation: 31

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

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 :)

Upvotes: 0

Views: 555

Answers (2)

Ergest Basha
Ergest Basha

Reputation: 8973

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

Upvotes: 0

Ihor Konovalenko
Ihor Konovalenko

Reputation: 1407

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).

Upvotes: 2

Related Questions