Trouble getting correct count with a join

Question

I'm trying to calculate the goals and assists for every player on a team using the tables below. A player can play on multiple teams in a single season. My results count the total assists from all teams. How can I limit assists to a single team?

My query is included below. The results I'm expecting are 2 goals and 1 assist for Gretzky. I don't want assist id 3 included in the results.

mysql> select * from players;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | Wayne      | Gretzky   |
|  2 | Mario      | Lemieux   |
|  3 | Mark       | Messier   |
+----+------------+-----------+

mysql> select * from teams;
+----+-----------+
| id | team_name |
+----+-----------+
|  1 | Oilers    |
|  2 | Penguins  |
|  3 | Kings     |
+----+-----------+

mysql> select * from goals;
+----+-----------+---------+---------+
| id | player_id | team_id | game_id |
+----+-----------+---------+---------+
|  1 |         1 |       1 |       1 |
|  2 |         1 |       1 |       1 |
|  3 |         3 |       1 |       1 |
|  4 |         2 |       2 |       1 |
|  5 |         3 |       3 |       2 |
+----+-----------+---------+---------+

mysql> select * from assists;
+----+---------+-----------+
| id | goal_id | player_id |
+----+---------+-----------+
|  1 |       1 |         3 |
|  2 |       3 |         1 |
|  3 |       5 |         1 |
+----+---------+-----------+

mysql> select players.id, players.last_name,
    -> count(distinct goals.id) as 'Goals',
    -> count(distinct assists.id) as 'Assists'
    -> from players
    -> join goals on players.id = goals.player_id
    -> left join assists on players.id = assists.player_id
    -> join teams on goals.team_id = teams.id
    -> where teams.id = 1
    -> group by players.id;
+----+-----------+-------+---------+
| id | last_name | Goals | Assists |
+----+-----------+-------+---------+
|  1 | Gretzky   |     2 |       2 |
|  3 | Messier   |     1 |       1 |
+----+-----------+-------+---------+

Answer 1

I think the most straightforward approach to what you're attempting to do is to just use correlated sub queries.

So, the first example just below returns the results you're looking for. You could easily modify it to exclude the rows with zero goals and assists.

It uses the team_id value in each subquery, but you can supply that with a variable or parameter, as shown, so that you only need to specify the value once:

set @team_id := 2;

select
    p.id as player_id
    , p.last_name
    , (
        select count(*)
        from goals
        where player_id = p.id
        and team_id = @team_id
    ) as goals
    , (
        select count(*)
        from assists
        inner join goals on assists.goal_id = goals.id
        where assists.player_id = p.id
        and goals.team_id = @team_id
    ) as assists
from players p

For team 1:

+----+-----------+-------+---------+
| id | last_name | Goals | Assists |
+----+-----------+-------+---------+
|  1 | Gretzky   |     2 |       1 |
|  2 | Lemieux   |     0 |       0 |
|  3 | Messier   |     1 |       1 |
+----+-----------+-------+---------+

For team 2:

+----+-----------+-------+---------+
| id | last_name | Goals | Assists |
+----+-----------+-------+---------+
|  1 | Gretzky   |     0 |       0 |
|  2 | Lemieux   |     1 |       0 |
|  3 | Messier   |     0 |       0 |
+----+-----------+-------+---------+

For team 3:

+----+-----------+-------+---------+
| id | last_name | Goals | Assists |
+----+-----------+-------+---------+
|  1 | Gretzky   |     0 |       1 |
|  2 | Lemieux   |     0 |       0 |
|  3 | Messier   |     1 |       0 |
+----+-----------+-------+---------+

Epilogue

From the perspective of trying to do this with fewer subqueries and/or with aggregate queries, you have a couple of issues going on with your first attempt.

One issue is that your query probably won't work correctly if you don't include all of the fields in your group by clause even though MySQL won't gripe at you about that like (most?) other databases will.

Also, because records in both your assists and players tables are only indirectly related to teams through the goals table, it's pretty tough to get an independent rollup on both goals and assists with just one query.

As an illustration of sorts, other early answers to this, including my first quick shot at it, had a couple of issues:

• If a player had assists for a team, but didn't have any goals for that team, the queries couldn't return any results for that player and team combination. The results were incomplete.

• If a player had goals for a team, but had no assists for that team, the queries would still return a positive number for assists when they should have returned zero. The results were actually wrong, not just incomplete.

Just below is a slightly more correct, but still incomplete solution. It correctly indicates if a player has no assists, albeit by returning null instead of 0 which is unfortunate.

But it's still a partial solution because if a player doesn't have any goals for a team, you still won't see any assists for that player and team combination.

This uses a subquery as a virtual table that aggregates assists per player and team, and the left outer join to the subquery is what makes it return a result if there are goals, but no assists.

select
    p.id as player_id
    , p.last_name
    , count(g.game_id) as goals
    , a.assists
from players p
inner join goals g on p.id = g.player_id
left join (
    select
        assists.player_id
        , goals.team_id
        , count(assists.id) as assists
    from assists
    inner join goals on assists.goal_id = goals.id
    group by player_id, team_id, assists.id
) a
on g.player_id = a.player_id and g.team_id = a.team_id
where g.team_id = 1
group by player_id, last_name, g.team_id

That query returns these results:

+----+-----------+-------+---------+
| id | last_name | Goals | Assists |
+----+-----------+-------+---------+
|  1 | Gretzky   |     2 |       1 |
|  3 | Messier   |     1 |       1 |
+----+-----------+-------+---------+

Run this for team 2, and you get these next results, indicating that Lemieux doesn't have any assists for team 2, but returning no results at all for the other two players, who have no assists and no goals for team 2:

+----+-----------+-------+---------+
| id | last_name | Goals | Assists |
+----+-----------+-------+---------+
|  2 | Lemieux   |     1 |    null |
+----+-----------+-------+---------+

Finally, run it for team 3, and you get these next results, indicating that Messier doesn't have any assists for team 3. But Gretzky is missing, even though he does have an assist for team 3, because he doesn't have any goals for team 3. So the solution is not complete:

+----+-----------+-------+---------+
| id | last_name | Goals | Assists |
+----+-----------+-------+---------+
|  3 | Messier   |     1 |    null |
+----+-----------+-------+---------+