Subqueries in Select for Calculations
Subqueries in SELECT are a useful way to create calculated columns in a query. A subquery in SELECT can be treated as a single numeric value to use in your calculations. When writing queries in SELECT, it's important to remember that filtering the main query does not filter the subquery -- and vice versa.
In the previous exercise, you created a column to compare each league's average total goals to the overall average goals in the 2013/2014 season. In this exercise, you will add a column that directly compares these values by subtracting the overall average from the subquery.
This exercise is part of the course
Data Manipulation in SQL
Exercise instructions
- Select the average goals scored in a match for each league in the main query.
- Select the average goals scored in a match overall for the 2013/2014 season in the subquery.
- Subtract the subquery from the average number of goals calculated for each league.
- Filter the main query so that only games from the 2013/2014 season are included.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
-- Select the league name and average goals scored
___ AS league,
ROUND(___(___ + m.away_goal),2) AS avg_goals,
-- Subtract the overall average from the league average
ROUND(AVG(___ + m.away_goal) ___
(SELECT ___(___ + away_goal)
FROM match
WHERE ___ = ___),2) AS diff
FROM league AS l
LEFT JOIN match AS m
ON l.country_id = m.country_id
-- Only include 2013/2014 results
WHERE ___ = ___
GROUP BY l.name;