Add a subquery to the SELECT clause

Subqueries in SELECT statements generate a single value that allow you to pass an aggregate value down a data frame. This is useful for performing calculations on data within your database.

In the following exercise, you will construct a query that calculates the average number of goals per match in each country's league.

This exercise is part of the course

Data Manipulation in SQL

View Course

Exercise instructions

In the subquery, select the average total goals by adding home_goal and away_goal.
Filter the results so that only the average of goals in the 2013/2014 season is calculated.
In the main query, select the average total goals by adding home_goal and away_goal. This calculates the average goals for each league.
Filter the results in the main query the same way you filtered the subquery. Group the query by the league name.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

SELECT 
	l.name AS league,
    -- Select and round the league's total goals
    ROUND(___(___ + m.away_goal), 2) AS avg_goals,
    -- Select & round the average total goals for the season
    (SELECT ___(___(___ + away_goal), 2) 
     FROM match
     ___ ___ = ___) 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 ___ = ___
GROUP BY ___;

Edit and Run Code