Intermediate · Lesson 9 of 20

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.

Check your understanding

Which query returns the average number of employees per department?

Examples run on the employees sample database. Open any of them in the playground to experiment.

Practice it on a real question

All Subqueries questions

Practice on your iPhone

Query Dojo for iPhone has 85 interview questions, timed mock interviews and a tile builder, so you can write queries without a keyboard.

Get the app