Add a subquery in SELECT

In the previous exercise, you added a subquery to the FROM statement and selected the stages where the number of average goals in a stage exceeded the overall average number of goals in the 2012/2013 match season. In this final step, you will add a subquery in SELECT to compare the average number of goals scored in each stage to the total.

This exercise is part of the course

Data Manipulation in SQL

View Course

Exercise instructions

Create a subquery in SELECT that yields the average goals scored in the 2012/2013 season. Name the new column overall_avg.
Create a subquery in FROM that calculates the average goals scored in each stage during the 2012/2013 season.
Filter the main query for stages where the average goals exceeds the overall average in 2012/2013.

Hands-on interactive exercise

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

SELECT 
	-- Select the stage and average goals from s
	___,
    ROUND(___,2) AS avg_goal,
    -- Select the overall average for 2012/2013
    (___ ___(___ + away_goal) ___ ___ WHERE ___ = ___) AS ___
FROM 
	-- Select the stage and average goals in 2012/2013 from match
	(SELECT
		 ___,
         ___(___ + away_goal) AS avg_goals
	 FROM ___
	 WHERE season = ___
	 GROUP BY stage) AS s
WHERE 
	-- Filter the main query using the subquery
	s.avg_goals ___ (SELECT ___(___ + away_goal) 
                    FROM match WHERE season = ___);

Edit and Run Code