Subqueries with Aggregate Functions
What you'll learn: How to use subqueries containing aggregate functions to filter rows based on calculated thresholds like averages or maximums.
The Core Idea
Sometimes you need to compare individual rows against a calculated value from your entire dataset. For example, "find all employees earning more than the average salary" or "show products priced above the maximum price in their category." You can't do this with a simple WHERE clause because aggregates like AVG() or MAX() operate on groups of rows, not individual ones.
This is where subqueries with aggregate functions shine: they let you calculate a value first (in the subquery), then use that result to filter your main query.
How It Works
Think of it like a two-step process:
- The subquery calculates a single aggregate value (like average, maximum, or sum)
- The main query uses that value as a filter condition
SELECT employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Here, the subquery (SELECT AVG(salary) FROM employees) runs first and returns one number—the average salary. Then the outer query uses that number to filter employees.
Common Patterns
- Above average:
WHERE value > (SELECT AVG(value) FROM table) - Below maximum:
WHERE value < (SELECT MAX(value) FROM table) - Equal to minimum:
WHERE value = (SELECT MIN(value) FROM table)
The subquery must return a single value when used with comparison operators like >, <, or =.
Key Takeaway: Subqueries with aggregate functions let you filter rows by comparing them against calculated thresholds (average, max, min) that you couldn't reference directly in a simple WHERE clause.