DEV Community

loading...

Day 62 Of 100DaysOfCode: Add a Subquery To The SELECT Clause

iamdurga profile image Durga Pokharel ・2 min read

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;
Enter fullscreen mode Exit fullscreen mode

Discussion (0)

pic
Editor guide