Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Exercises
9. The biggest orchestras
Correlated Subqueries

Instruction

Great! Now we'll use the previous query in a new exercise.

Exercise

Show the name and number of members for each orchestra that has more members than the average membership of all orchestras in the table.

Stuck? Here's a hint!

First, write the query from the previous exercise and compute the average number of members in all orchestras. This will be our subquery:

SELECT 
  AVG(d.count) 
FROM 
  (SELECT orchestra_id, COUNT(id) FROM members GROUP BY 1) d;

Then, write a query which computes the number of members in each orchestra. This will be the outer query.

Finally, use the subquery in the HAVING condition of the outer query. Note that this way you'll have nested subqueries.