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.
Questo esercizio fa parte del corso
Data Manipulation in SQL
Istruzioni dell'esercizio
- 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.
Esercizio pratico interattivo
Prova a risolvere questo esercizio completando il codice di esempio.
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;