How to join AND sum values of two columns?-CodePudding

I have the following two tables

TeamsTable

TeamID	TeamName
1	Name1
2	Name2
3	Name3
...	...

and

GameScoresTable

GameID	HomeTeam(FK)	AwayTeam(FK)	HomeTeamScore	AwayTeamScore
1	1	2	50	60
2	2	3	70	80
3	3	4	70	80
...	...	...	...	...

I want to get a table like this:

FinalTable

GameID	HomeTeam	AwayTeam	TotalScore
1	Name1	Name2	110
2	Name2	Name3	150
3	Name3	Name4	150
...	...	...	...

I tried the following query, but it doesn't work.

SELECT 
GameScores.GameID
,TeamH.TeamName as HomeTeam
,TeamA.TeamName as AwayTeam
,SUM(GameScores.HomeTeamScore   GameScores.AwayTeamScore)
FROM GameScores 
    INNER JOIN Teams TeamH ON GameScores.HomeTeam=TeamH.TeamID
    INNER JOIN Teams TeamA ON GameScores.AwayTeam=TeamA.TeamID
    GROUP   BY GameID

Essentially, I want to get the HomeTeam and AwayTeam columns to show their proper names rather than the foreign key value and want the last column to show their combined score.

CodePudding user response：

You don't want to aggregate your data. Just use to get the sum.

SELECT 
     GameScores.GameID
    ,TeamH.TeamName AS HomeTeam
    ,TeamA.TeamName AS AwayTeam
    ,GameScores.HomeTeamScore   GameScores.AwayTeamScore AS totalscore
FROM GameScores 
    INNER JOIN Teams TeamH ON GameScores.HomeTeam=TeamH.TeamID
    INNER JOIN Teams TeamA ON GameScores.AwayTeam=TeamA.TeamID