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
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 ;