Double grouping?

[Tinnin]

Hi all,

Regarding a football (soccer) league:

I am trying to find the top goalscorer for each team in the league in December 2012.

Each time a player scores a new row is created in the table "Scored" with their name in it and their corresponding team and the date the goal was scored.

The query I have so far is as follows:

SELECT TeamID, PlayerID, COUNT(*) AS Total
FROM Scored
WHERE 
MONTH(GoalDate)=12 AND YEAR(GoalDate)=2012
GROUP BY PlayerID

This gives me the total for each player but anything I try to find only the top goal scorer for each team has not worked. I am using Navicat for MySQL and apparantly I can't create temporary tables using it.

Does anyone have any suggestions?

Many thanks.

[pritaeas]

Perhaps a subselect?

SELECT * FROM (
    SELECT TeamID, PlayerID, COUNT(*) AS Total
    FROM Scored
    WHERE MONTH(GoalDate)=12 AND YEAR(GoalDate)=2012
    GROUP BY PlayerID
    ORDER BY TeamID, Total DESC
    ) T
GROUP BY TeamID, Total

[Tinnin]

Thanks pritaeas,

That groups them into the TeamIDs but is there a way to pull out just the MAX(Total) for each TeamID?

[pritaeas]

Can you provide some test data ? An sql script preferably.

[Tinnin]

The result of the query:

TeamID PlayerID Total
A James 1
A Albert 3
A Steven 23
A Sarah 28
A Collin 34
B Ruth 4
B Peter 7
B William 8
B Rupert 3
C Frank 1
C Alfred 2
C Carl 4
C Terry 8
C Angela 10
C Robert 11
C Christopher 12
C Norman 14
C Elizabeth 18
C David 28
C Norma 29
D Ralph 1
D Christian 10
D Dennis 41
E Lucy 7

[Tinnin]

This seems to have done the trick:

SELECT TeamID, PlayerID, MAX(Total) AS MaxTotal FROM (
    SELECT TeamID, PlayerID, COUNT(*) AS Total
    FROM Scored
    WHERE MONTH(CollectionDate)=12 AND YEAR(CollectionDate)=2012
    GROUP BY PlayerID
    ORDER BY TeamID, Total DESC
) T
GROUP BY TeamID

[Tinnin]

Or more simply:

SELECT TeamID, PlayerID, MAX(Total) AS MaxTotal FROM (
    SELECT TeamID, PlayerID, COUNT(*) AS Total
    FROM Scored
    WHERE MONTH(CollectionDate)=12 AND YEAR(CollectionDate)=2012
    GROUP BY PlayerID
) T
GROUP BY TeamID

[Tinnin]

Hmmm. It doesn't match up to the playerID this way though. Back to the drawing board.

[Tinnin]

Done:

SELECT TeamID, PlayerID, MAX(Total) AS MaxTotal FROM (
    SELECT TeamID, PlayerID, COUNT(*) AS Total
    FROM Scored
    WHERE MONTH(GoalDate)=12 AND YEAR(GoalDate)=2012
    GROUP BY PlayerID
    ORDER BY Total DESC
) T
GROUP BY TeamID