MySQL count rows on multiple grouped columns

Question

I have a following structure on my MySQL tables:

-------------------------------------------
                 Goal
-------------------------------------------
GoalID  |  GameID  |  ScorerID  |  AssistID
-------------------------------------------
   1          1          1            2
   2          1          2            3
   3          1          2           null
   4          1          3            2
   5          2          1            3
   6          2          1           null


----------------------
       Player
----------------------
PlayerID  |  LastName
----------------------
   1            AB    
   2            CD  
   3            EF  

now, what I want to achieve is the following:

-------------------------------------------
GameID  |  Player  |  Goals  |  Assists
-------------------------------------------
   1         AB          1         0
   1         CD          2         2
   1         EF          1         1
   2         AB          2         0
   2         EF          0         1

This is what I have tried but it gives me the wrong results:

SELECT Goal.GoalID,
   Goal.GameID,
   COUNT(Scorer.PlayerID) AS Goals,
   COUNT(Assist.PlayerID) AS Assists,
   Player.LastName AS Player
FROM Goal
LEFT JOIN Player Player
       ON Player.PlayerID = Goal.ScorerID
       OR Player.PlayerID = Goal.AssistID
LEFT JOIN Player Scorer
       ON Scorer.PlayerID = Goal.ScorerID
LEFT JOIN Player Assist
       ON Assist.PlayerID = Goal.AssistID
GROUP BY Player,
         Goal.GameID
ORDER BY Goal.GameID,
         Goal.GoalID

I'm pretty sure there's a simple way to get the result but I just can't get my head around this one.

Answer 1

You can accomplish this by using conditional aggregation. To do so, you join the player and goal tables on the condition that the player id matches the scorerid or assist id, and then get the SUM() of situations where the player id is in the score or assist column, and ultimately group by gameID and playerID. Like this:

SELECT g.gameID, p.lastName AS Player, SUM(g.scorerID = p.playerID) AS goals, SUM(g.assistID = p.playerID) AS assists
FROM goal g
JOIN player p ON p.playerID = g.scorerID OR p.playerID = g.assistID
GROUP BY g.gameID, p.playerID;

Here is an SQL Fiddle example.

It is important to note that this example assumes the player has at least one goal or at least one assist. If you want players who have neither, you can use an outer join.

Answer 2

http://sqlfiddle.com/#!9/88479/7

SELECT g.GameID, 
  p.LastName, 
  SUM(IF(p.id=g.ScorerID,1,0)) as Goals, 
  SUM(IF(p.id=g.AssistID,1,0)) as Assists
FROM goal g
LEFT JOIN player p
ON p.id = g.ScorerID
  OR p.id = g.AssistID
GROUP BY g.GameID,p.id