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.
Bu egzersiz
Data Manipulation in SQL
kursunun bir parçasıdırEgzersiz talimatları
- In the subquery, Select the average total goals by adding
home_goalandaway_goal, then - 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_goalandaway_goal. This calculates the average goals for each league, then - Filter the results in the main query the same way you filtered the subquery.
GROUPthe queryBYthe leaguename.
Uygulamalı interaktif egzersiz
Bu örnek kodu tamamlayarak bu egzersizi bitirin.
SELECT
l.name AS league,
-- Round the average of the league's total goals
ROUND(___(___ + m.away_goal), 2) AS avg_goals,
-- Select and 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 ___;