How to join AND sum values of two columns?

January 28, 2023

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.

>Solution :

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