Skip to content
Advertisement

Sum two column values on the basis of two name column

I am new to Postgres database. I am given 4 columns homeTeamName, homeTeamGoals, awayTeamGoals, awayTeamName. I want to find out the total goal(homeTeamGoals + awayTeamGoals) scored by each team.

select distinct hometeamname, SUM(hometeamgoals + awayteamgoals) as goals
    from worldcupsmatches

    where worldcupsmatches is not null
    group by hometeamname
order by goals desc 

the database is as follows

database here

I think my solution is wrong. So looking for a better way

Advertisement

Answer

You can separate the table content as below, and then apply SUM function :

select teamname, sum(goals) goals
from (
  select hometeamname as teamname, hometeamgoals as goals
      from worldcupsmatches
      where worldcupsmatches is not null    
  union all
  select awayteamname, awayteamgoals
      from worldcupsmatches
      where worldcupsmatches is not null
) t
group by teamname
order by goals desc, teamname asc
;

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement