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
Exercise instructions
- In the subquery, select the average total goals by adding
home_goal
andaway_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
andaway_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 ___;