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,
    when teamid = team1
 end as score1, team2,
    when teamid = team2
 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.