This is my 62th day of #100daysofcode and #Python learned. Like yesterday today also continued to learned SQL's properties from Datacamp. Also learned more about algorithm from Algorithmic Toolbox.

Add a subquery to the SELECT clause

Subqueries in SELECT statements generate a single value that allow to pass an aggregate value down a data frame. This is useful for performing calculations on data within our database. In the following code, we will construct a query that calculates the average number of goals per match in each country's league.

SELECT 
    l.name AS league,
    -- Select and round the league's total goals
    ROUND(AVG(m.home_goal + m.away_goal),2) AS avg_goals,
    -- Select and round the average total goals
    (SELECT ROUND(AVG(home_goal + away_goal),2) 
     FROM match
     WHERE season = '2013/2014') AS overall_avg
FROM league AS l
LEFT JOIN match AS m
ON l.country_id = m.country_id
-- Filter for the 2013/2014 season
WHERE m.season = '2013/2014'
GROUP BY l.name;

Day 62 Of #100DaysOfCode and #Python
* Algorithmic Toolbox
* SQL(Add a subquery to the SELECT clause) from https://t.co/RHm2OCSMY9Datacamp#WomenWhoCode #CodeNewbie #beginner #DEVCommunity pic.twitter.com/foe5s469ye

— Durga Pokharel (@mathdurga) February 28, 2021