Day 61 Of 100DaysOfCode : COUNT And CASE WHEN With Multiple Conditions

Today I completed Day 61 of #100daysofcode and #python learning. Like previous today also continue to learned from Datacamp about SQL properties (CASE Statements in aggregate, COUNT and CASE WHEN with multiple conditions). Tried to solve assignment from Coursera.

SQL code

In the code we used country and match table to determine the total number of matches won by the home team in each country during the 2012/2013, 2013/2014, and 2014/2015 seasons.

SELECT 
    c.name AS country,
    -- Sum the total records in each season where the home team won
    SUM(CASE WHEN m.season = '2012/2013' AND m.home_goal > m.away_goal 
        THEN 1 ELSE 0 END) AS matches_2012_2013,
    SUM(CASE WHEN m.season = '2013/2014' AND m.home_goal > m.away_goal 
        THEN 1 ELSE 0 END) AS matches_2013_2014,
    SUM(CASE WHEN m.season = '2014/2015' AND m.home_goal > m.away_goal 
        THEN 1 ELSE 0 END) AS matches_2014_2015
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
-- Group by country name alias
GROUP BY country;

Day 61 Of #100DaysOfCode and #Python
* https://t.co/RdUBj1KUUxAlgorithmic Toolbox
* SQL (COUNT and CASE WHEN with multiple conditions)#womenintech #100DaysOfCode #CodeNewbie #DEVCommunity #beginner pic.twitter.com/z6Ul8aIeaK

— Durga Pokharel (@mathdurga) February 27, 2021