Subqueries inside SELECT clause

 

Subqueries inside SELECT clause

Second most common: Inside SELECT

Usage:

• Returns s single value
• Include aggregate values to compare to individuals values
• Used in mathematical calculations
• Deviation from average

Keep things in mind:

• Need to return a single value
• Properly filter both the main and the subquery

Some queries can be written using either a join or a subquery

SELECT DISTINCT continent,

                (SELECT COUNT(*)

                  FROM states

                   WHERE prime_ministers.continent = states.continent) AS countries_num

FROM prime_ministers;

Examples:

Joins: To get summarized results from multiple table we used GROUP BY and INNER JOIN.

Question: Select the top nine countries in terms of number of cities (popular cities) appearing in the cities table.

SELECT countries.name AS country, COUNT(*) AS cities_num

  FROM cities

    INNER JOIN countries

    ON countries.code = cities.country_code

GROUP BY country

ORDER BY cities_num DESC, country

LIMIT 9;

/* 

SELECT ___ AS ___,

  (SELECT ___

   FROM ___

   WHERE countries.code = cities.country_code) AS cities_num

FROM ___

ORDER BY ___ ___, ___

LIMIT 9;

*/

Subquery: Convert the GROUP BY code to use a subquery inside of SELECT

/*

SELECT countries.name AS country, COUNT(*) AS cities_num

  FROM cities

    INNER JOIN countries

    ON countries.code = cities.country_code

GROUP BY country

ORDER BY cities_num DESC, country

LIMIT 9;

*/

SELECT countries.name AS country,

  -- Subquery

  (SELECT COUNT(*)

   FROM cities

   WHERE countries.code = cities.country_code) AS cities_num

FROM countries

ORDER BY cities_num DESC, country

LIMIT 9;

Question:

Construct a query that calculates the average number of goals per match in each country's league.

SELECT 

    l.name AS league,

    -- Select and round the league's total goals

    ROUND(AVG(m.home_goal + m.away_goal), 2) AS avg_goals,

    -- Select & round the average total goals for the season

    (SELECT ROUND(AVG(home_goal + away_goal), 2) 

     FROM match

     WHERE season = '2013/2014') 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 season = '2013/2014'

GROUP BY league;

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.

SELECT

    -- Select the league name and average goals scored

    l.name AS league,

    ROUND(AVG(m.home_goal + m.away_goal),2) AS avg_goals,

    -- Subtract the overall average from the league average

    ROUND(AVG(m.home_goal + m.away_goal) -

        (SELECT AVG(home_goal + away_goal)

         FROM match 

         WHERE season = '2013/2014'),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 season = '2013/2014'

GROUP BY l.name;

Q:

Extract the average number of home and away team goals in two SELECT subqueries. Calculate the average home and away goals for the specific stage in the main query.Filter both subqueries and the main query so that only data from the 2012/2013 season is included.Group the query by the m.stage column.

ECT 

    -- Select the stage and average goals for each stage

    m.stage,

    ROUND(AVG(m.home_goal + m.away_goal),2) AS avg_goals,

    -- Select the average overall goals for the 2012/2013 season

    ROUND((SELECT AVG(home_goal + away_goal) 

           FROM match 

           WHERE season = '2012/2013'),2) AS overall

FROM match AS m

-- Filter for the 2012/2013 season

WHERE season = '2012/2013'

-- Group by stage

GROUP BY m.stage

ORDER BY m.stage;