CODEWITHSUNDEEP
mysqlsqlselectsummax
Elips
Elips

Reputation: 23

Select Max() of SUM() for a Group

SELECT tname , pname , SUM(Goals)
FROM team as t , player as p , matches as m 
WHERE t.TID=p.TID AND p.PNB=m.PNB
Group BY pname , tname
ORDER BY `SUM(Goals)`  DESC

I need to Select only the player who have more goals in each team.Example it will shown only Luis Suarez from F.C Barcelona and Munir fROM SEVILLA ....

I need to Select only the player who have more goals in each team.Example it will shown only Luis Suarez from F.C Barcelona and Munir fROM SEVILLA ....

Upvotes: 0

Views: 75

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Use proper, explicit, standard JOIN syntax. Commas were superseded over 20 years ago.

Then, you want window functions:

SELECT tp.*
FROM (SELECT t.tname, p.pname, SUM(m.Goals) as goals,
             RANK() OVER (PARTITION BY t.tname ORDER BY SUM(m.Goals) DESC) as seqnum
      FROM matches m JOIN
           team t
           ON t.TID = p.TID JOIN
           player p  
           ON p.PNB = m.PNB
      GROUP BY p.pname, t.tname
     ) tp
WHERE seqnum = 1
ORDER BY goals DESC;

This returns all players with the highest values. If you want just one row, use ROW_NUMBER() instead of RANK().

Upvotes: 2

Related Questions