SQLzoo JOIN tutorial #13

Question

I have been working on the SQLzoo problems but having trouble with the last one in the JOIN tutorial question #13 - List every match with the goals scored by each team as shown.

Link: http://sqlzoo.net/wiki/The_JOIN_operation

In the sample code they gave, it uses a case. I modified it like this:

SELECT game.mdate, game.team1,
  CASE WHEN goal.teamid=game.team1 THEN 1 ELSE 0 END score1, game.team2,
  CASE WHEN goal.teamid=game.team2 THEN 1 ELSE 0 END score2
  FROM game, goal WHERE game.id=goal.matchid
GROUP BY game.mdate, goal.matchid, game.team1, game.team2

They suggest that using a SUM function on the score1/score2 will provide the answer. I am confused as to how to use the SUM function on these 2 columns that are created within the SQL.

Could anyone provide a hint as to how to do or mention in broad terms how to write this SQL query in a better fashion?

Answer 1

This worked for me with correction of left join.

SELECT mdate, team1,
SUM(CASE WHEN teamid = team1 THEN 1 ELSE 0 END) score1,
team2,
SUM(CASE WHEN teamid = team2 THEN 1 ELSE 0 END) score2
FROM game LEFT JOIN goal ON matchid = id GROUP BY mdate, matchid, team1, team2