Great! Now we'll use the previous query in a new 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.
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
OrchestraId,
COUNT(Id)
FROM Members
GROUP BY OrchestraId
) 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.
