CODEWITHSUNDEEP
sqlsql-servert-sqlaggregate-functions
frango_mints
frango_mints

Reputation: 59

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?

Upvotes: 0

Views: 253

Answers (2)

Henning Koehler
Henning Koehler

Reputation: 2637

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

Upvotes: 0

molig
molig

Reputation: 324

One way would be to create two intermediate results. In the first you aggregate over the home team id, in the second over the guest team id. This leaves you with two intermediate result sets with your scores for each teams home matches and one for the scores for each teams guest matches. You can sum the intermediate results of both tables up for each team id and write it to your result table.

Upvotes: 2

Related Questions