MySQL counting rows in multiple grouped columns
I have the following structure in 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 I want to achieve 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 tried, but it gives me 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 sure there is an easy way to get the result, but I just can't get around this thread.
source to share
You can accomplish this using conditional aggregation. To do this, you join the player and goal tables, assuming the player id is the same as the scorerid or sub id, then get SUM () of situations where the player id is in the score or help column, and the gameID and playerID are ultimately grouped together. 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 a SQL Fiddle example .
It's important to note that this example assumes the player has at least one goal or at least one help. If you want players who don't have them, you can use outer join.
source to share
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
source to share
Another way of solving this issue is CROSS JOIN
players and goals, and then count the number of intersections on Scorers
and Assists
using a match CASE..WHEN
. HAVING
used to eliminate players who have not contributed to the account.
SELECT
Goal.GameID,
Player.LastName AS Player,
SUM(CASE WHEN Goal.ScorerId = Player.PlayerID THEN 1 ELSE 0 END) AS Goals,
SUM(CASE WHEN Goal.AssistId = Player.PlayerID THEN 1 ELSE 0 END) AS Assists
FROM Goal
CROSS JOIN Player
GROUP BY Player.LastName,
Goal.GameID
HAVING Goals > 0 OR Assists > 0
ORDER BY Goal.GameID,
Player.LastName;
source to share
There are several ways to solve this problem. I believe that something like this would work well:
SELECT
GameId,
Lastname,
Sum(Goals) as Goals,
Sum(Assists) as Assists
FROM
(
SELECT GameID, Player.Lastname, 1 as Goals, 0 as Assists
FROM Goal
INNER JOIN Player ON goal.scorerID = Player.PlayerID
UNION
SELECT GameID, Player.Lastname, 0 as Goals, 1 as Assists
FROM Goal
INNER JOIN Player ON goal.AssistID = Player.PlayerID
) Games
GROUP BY GameID, LastName
source to share
Here's a fairly simple way to achieve this:
select GameId, LastName, goals, assist
from
(
select GameId, player, sum(goals) as goals, sum(assist) as assist
from
(
select GameId, ScorerId as player, count(*) as goals, 0 as assist
from Goal
group by GameId, ScorerId
union all
select GameId, AssistId, 0 as goals, count(*) as assist
from Goal
where AssistId is not null
group by GameId, AssistId
) as q
group by GameId, player
) as q2
inner join
Player as t
on (q2.player=t.PlayerId)
order by GameId, LastName
source to share