Troubles with Case and Creating a table on SQL

Question

I'm trying to create a table where I can pull the result of the winner of several games, I'm trying to use the CASE WHEN funtion in DBeaver but it seems not working, it seems to be a syntax error but I don't know how to make it run.

DROP TABLE  results;
CREATE TABLE results AS
SELECT match_api_id, season, Country.name, League.name, stage, Match.home_team_api_id,Match.away_team_api_id, home_team_goal, away_team_goal
    CASE winner
        WHEN home_team_goal > away_team_goal THEN 'Home Wins'
        WHEN away_team_goal > home_team_goal THEN 'Away Wins'
        [ELSE 'Tie']
    END 
    
FROM Match
    INNER JOIN Country ON Match.country_id = Country.id 
    INNER JOIN League ON Match.league_id = League.id 
    ;
    
SELECT * FROM results ;

Answer 1

You should make sure the select statement works first. I guess the case name is placed at the wrong position. Try this.

SELECT match_api_id, season, Country.name, 
  League.name, stage, 
  Match.home_team_api_id,Match.away_team_api_id, 
  home_team_goal, away_team_goal,
  CASE 
    WHEN home_team_goal > away_team_goal THEN 'Home Wins'
    WHEN away_team_goal > home_team_goal THEN 'Away Wins'
    [ELSE 'Tie']
  END AS Winner 
FROM Match
  INNER JOIN Country ON Match.country_id = Country.id 
  INNER JOIN League ON Match.league_id = League.id 

I added "AS" to make it more understandable that the case statement result is a column named "Winner".