frango_mints frango_mints - 12 days ago 5
SQL Question

Populate Table with aggregated data from other Table

I am trying to populate the LeagueStandings table with aggregate data from the MatchResults table.

CREATE TABLE [LeagueStandings] (
[TeamID] INTEGER NOT NULL PRIMARY KEY
,[GoalsScored] INTEGER NOT NULL
,[WinLoss] INTEGER NOT NULL
)
GO

CREATE TABLE [MatchResults] (
[MatchID] INTEGER NOT NULL PRIMARY KEY
,[HomeTeamID] INTEGER NOT NULL
,[HomeTeamGoalsScored] INTEGER NOT NULL
,[AwayTeamID] INTEGER NOT NULL
,[AwayTeamGoalsScored] INTEGER NOT NULL
)
GO


The LeagueStandings.GoalsScored column needs to be the total number of goals scored by a team, minus the total number of goals scored against the team.

Likewise the LeagueStandings.WinLoss column needs to be the number of wins minus the number of losses. A win is when a team scores more goals than their opponent. There will never be a tie.

I am stuck because the same team can be considered 'home' and 'away' for different matches, how would the LeagueStandings.GoalsScored and LeagueStandings.WinLoss table be populated?

Answer
select TeamID, sum(GoalsScored) as GoalsScored, sum(WinLoss) as Winloss
from (
  select
    HomeTeamID as TeamID,
    HomeTeamGoalsScores as GoalsScored,
    HomeTeamGoalsScores - AwayTeamGoalsScored as WinLoss
  from MatchResults
  union all
  select
    AwayTeamID as TeamID,
    AwayTeamGoalsScores as GoalsScored,
    AwayTeamGoalsScores - HomeTeamGoalsScored as WinLoss
  from MatchResults) matches
group by TeamID