Subqueries in FROM

A subquery can also stand in for a table. Put it in FROM, give it a name, and query its results like any other table.

First, the headcount of each department:

SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id;

Now wrap it to get the average department size:

SELECT AVG(headcount) AS avg_size
FROM (
    SELECT department_id, COUNT(*) AS headcount
    FROM employees
    GROUP BY department_id
) AS dept_sizes;

This is how you aggregate an aggregate. AVG(COUNT(*)) isn't allowed, but a subquery in FROM gets you there in two steps.

The name after the closing parenthesis (dept_sizes) is required in many databases, so always add one.