Premier League 18/19 Stats Data

-- Count matches won and lost for each team
SELECT 
    team,
	COUNT(MatchID) AS MP,
    COUNT(CASE WHEN result = 'win' THEN 1 END) AS W,
	COUNT(CASE WHEN result = 'draw' THEN 1 END) AS D,
    COUNT(CASE WHEN result = 'loss' THEN 1 END) AS L,
	SUM(goals_for) AS GF,
	SUM(goals_against) AS GA,
	SUM(goal_difference) AS GD,
	SUM(point) AS points
FROM (
    SELECT 
        HomeTeam AS team,
		MatchID,
        CASE 
            WHEN FTHG > FTAG THEN 'win'
            WHEN FTHG < FTAG THEN 'loss'
            ELSE 'draw'
        END AS result,
		FTHG as goals_for,
		FTAG as goals_against,
		(FTHG - FTAG) AS goal_difference,
		CASE 
			WHEN FTHG > FTAG THEN 3 
	   		WHEN FTHG = FTAG THEN 1 
			ELSE 0 
		END AS point
    FROM 
        soccer

    UNION ALL

    SELECT 
        AwayTeam AS team,
		MatchID,
        CASE 
            WHEN FTAG > FTHG THEN 'win'
            WHEN FTAG < FTHG THEN 'loss'
            ELSE 'draw'
        END AS result,
		FTAG as goals_for,
		FTHG as goals_against,
		(FTAG - FTHG) AS goal_difference,
		CASE 
			WHEN FTHG < FTAG THEN 3 
	   		WHEN FTHG = FTAG THEN 1 
			ELSE 0 
		END AS point
    FROM 
        soccer
) AS matches
GROUP BY 
	team
ORDER BY 
	points DESC;