Finding total number of points scores by each team, but relying on foreign relations

user_13837521 0
Viewed 289

I am trying to craft a SQL query to solve the following problem. In the Game table, you see that there is away_team_id and home_team_id. Essentially, I want to find the total number of points for the away and home team for the specific game id.

In order to find the total points, you have to find the players from the respective team that participated in that specific game id. From there, you can sum their points, which would give the total number of points scored by that specific team.

However, I've relied on foreign keys to keep my database maintainable, which has made it a little difficult for me to compute these things. Does anyone mind showing me how I can accomplish this?

For example, I can do this to find the sum of only the away team or home team. I'd ideally want both results in one query. In the following example, 1 is the id of game and 2 is the id of the away team. This will give me the sum of points scored by the away team in game id 1. I can write another query and use the id of the home team, so I can accomplish this in two queries. However, how can I do this in one?

SELECT SUM(statistic.total_points)
FROM player, team, statistic
WHERE player.team_id = team.team_id
AND statistic.player_id = player.player_id
AND statistic.game_id = 1
AND team.team_id = 2

Game:

game_id
away_team_id
home_team_id

Statistic:

game_id
player_id
total_points

Team:

team_id
name

Player:

player_id
team_id

Thanks.

sql
1 Answers

I think you can calculate the statistics per team per game and then join to this table --

WITH gamestats AS (
  SELECT s.game_id, p.team_id, SUM(p.total_points) as total_points
  FROM statistic s
    INNER JOIN player p
      ON s.player_id = p.player_id
  GROUP BY s.game_id, p.team_id
)
SELECT g.game_id, ISNULL(away.total_points, 0), ISNULL(home.total_points, 0)
FROM game g
  LEFT JOIN gamestats away
    ON g.game_id = away.game_id
    AND g.away_team_id = away.team_id
  LEFT JOIN gamestats home
    ON g.game_id = home.game_id
    AND g.home_team_id = home.team_id
user_138783250
Related