CODEWITHSUNDEEP
mysqlsqldatabase
Yan Sarazin
Yan Sarazin

Reputation: 258

count on multiple joined tables

I have the following tables:

Players
- id
- name

Games
- id
- game_type_id
- season_id


Goals
- player_id
- game_id

Assists
- player_id
- game_id

I've been struggling trying to build a query/view that will give me the players' goal and assist counts for each season and game type. It's important to each player is listed for each season and game type, whether they have goals/assists or not.

The query will be used to create a view. Can it be done in one query?

UPDATE: Here's an SQL Fiddle with some production sample data.

Upvotes: 3

Views: 190

Answers (4)

pkuderov
pkuderov

Reputation: 3551

UPDATED

SELECT
    players.id,
    players.name,
    games.season_id,
    games.game_type_id,
    sum(CASE WHEN g.goals IS NULL THEN 0 ELSE g.goals END) AS goals,
    sum(CASE WHEN a.assists IS NULL THEN 0 ELSE a.assists END) AS assists
FROM players
CROSS JOIN games
LEFT JOIN (
  SELECT
    game_id, player_id,
    count(*) AS goals
  FROM goals
  GROUP BY
    game_id, player_id
) g ON
    g.player_id = players.id
    AND g.game_id = games.id
LEFT JOIN (
  SELECT
    game_id, player_id,
    count(*) AS assists
  FROM assists
  GROUP BY
    game_id, player_id
) a ON
    a.player_id = players.id
    AND a.game_id = games.id
GROUP BY
    players.id,
    players.name,
    games.season_id,
    games.game_type_id

This isn't very short version, but it should be right now.

Upvotes: 3

Taryn
Taryn

Reputation: 247650

try this:

select p.id,
  p.name,
  gm.season_id,
  gm.game_type_id,
  sum(gl.goalcount),
  sum(a.AssistCount)
from players p
left join
(
  select game_id, player_id, count(*) GoalCount
  from goals
  group by game_id, player_id
) gl
  on p.id = gl.player_id
left join
(
  select game_id, player_id, count(*) AssistCount
  from assists
  group by game_id, player_id
) a
  on p.id = a.player_id
left join games gm
  on gl.game_id = gm.id
  and a.game_id = gm.id
where season_id is not null
group by p.id,
  p.name,
  gm.season_id,
  gm.game_type_id

see SQL Fiddle with Demo

Upvotes: 1

ilan berci
ilan berci

Reputation: 3881

Awesome, I didn't think of the cross join, I came up with:

select players.id, games.game_type_id, games.season_id, g.c, a.c from players

left join (select count(id) as c, player_id, game_id from goals group by player_id, game_id) as g on g.player_id = players.id

left join (select count(id) as c, player_id, game_id from assists group by player_id, game_id) as a on a.player_id = players.id

right join games on games.id = g.game_id and games.id = a.game_id group by players.id, games.game_type_id, games.season_id;

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

SELECT
  players.id,
  players.name,
  games.season_id,
  games.game_type_id,
  SUM(COALESCE(assists.rows, 0))             AS assists,
  SUM(COALESCE(goals.rows,   0))             AS goals
FROM
  players
CROSS JOIN
  games
LEFT JOIN
  (SELECT game_id, player_id, COUNT(*) AS rows FROM assists GROUP BY game_id, player_id) AS assists
    ON assists.game_id = games.game_id
LEFT JOIN
  (SELECT game_id, player_id, COUNT(*) AS rows FROM goals   GROUP BY game_id, player_id) AS assists
    ON goals.game_id   = games.game_id
GROUP BY
  players.id,
  players.name,
  games.season_id,
  games.game_type_id

But it may improve in performance if you had a Seasons table and a GameType table.

SELECT
  players.id,
  players.name,
  seasons.id,
  game_types.id,
  SUM(COALESCE(assists.rows, 0))             AS assists,
  SUM(COALESCE(goals.rows,   0))             AS goals
FROM
  players
CROSS JOIN
  seasons
CROSS JOIN
  game_types
LEFT JOIN
  games
    ON  games.season_id = seasons.id
    AND games.game_type = game_types.id
LEFT JOIN
  (SELECT game_id, player_id, COUNT(*) AS rows FROM assists GROUP BY game_id, player_id) AS assists
    ON assists.game_id = games.game_id
LEFT JOIN
  (SELECT game_id, player_id, COUNT(*) AS rows FROM goals   GROUP BY game_id, player_id) AS assists
    ON goals.game_id   = games.game_id
GROUP BY
  players.id,
  players.name,
  seasons.id,
  game_types.id

Upvotes: 3

Related Questions