select matchid, mdate,count(teamid)from game join goal on id=matchid
where(team1='GER'or team2 ='GER')and teamid ='GER'groupby matchid;
List every match with the goals scored by each team as shown. This will use “CASE WHEN” which has not been explained in any previous exercises. Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.
SELECT mdate, team1,SUM(CASEWHEN teamid = team1 THEN1ELSE0END) score1,
team2,SUM(CASEWHEN teamid = team2 THEN1ELSE0END) score2
FROM game ga LEFTOUTERJOIN goal go ON(ga.id= go.matchid)GROUPBY mdate, team1, team2