sql query using aggregate function inside case

I’m trying to solve #13 on http://www.sqlzoo.net/wiki/The_JOIN_operation

“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.”

Here is what I wrote:

select distinct mdate, team1,
 case 
    when teamid = team1
      count(teamid)
 end as score1, team2,
 case 
    when teamid = team2
     count(teamid) 
 end as score2
from game join goal on id = matchid
group by matchid
order by mdate, matchid, team1, team2;

I am getting this error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘count(teamid) end as score1, team2, case when teamid = tea’ at line 4

what is wrong with my code?


Source: sql

Leave a Reply