sql - 根据两个名称列对两个列值求和

Question

我是 Postgres 数据库的新手。我有 4 列 homeTeamName、homeTeamGoals、awayTeamGoals、awayTeamName。我想找出每支球队的总进球数（homeTeamGoals + awayTeamGoals）。

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

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

数据库如下

我认为我的解决方案是错误的。所以寻找更好的方法

标签： sqlpostgresql

Answer 1

您可以如下分隔表格内容，然后应用 SUM 函数：

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
;