MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (
  HomeTeam VARCHAR(10),
  awayTeam VARCHAR(10),
  HomeTeamGoals int,
  AwayTeamGoals int
);
        
insert into test(hometeam, awayteam, hometeamgoals, awayteamgoals) values
('team1','team2',3,1),
('team1','team3',4,4),
('team4','team2',0,1)
select hometeam, sum(HomeTeamGoals) as homefor,sum(AwayTeamGoals) as homeagainst, 0 as awayfor, 0 as awayagainst
from test
group by hometeam

union all

select AwayTeam, 0,0, sum(HomeTeamGoals) as awayfor, sum(AwayTeamGoals) as awayagainst
from test
group by AwayTeam


hometeam
homefor
homeagainst
awayfor
awayagainst


team1
7
5
0
0

team4
0
1
0
0

team2
0
0
3
2

team3
0
0
4
4