How can I use a subquery to filter rows by an aggregate threshold?
Quick answer
Put the aggregate query in parentheses inside the outer query’s WHERE clause. For example, WHERE salary > (SELECT AVG(salary) FROM employees) compares each employee’s salary with the average across the employees table. The inner query supplies one comparison value; the outer query returns rows above it. The example below uses invented data and illustrates SQLite syntax. [S1]
The question, the details, the next step.
I have an employees table and want employees whose salary is higher than the overall average. How does a subquery help?
A subquery is a query nested inside another query. Here, the inner query calculates the average salary, and the outer query uses that value as its filter threshold. In SQLite, an aggregate SELECT can be used as a scalar subquery, so this pattern supplies one comparison value to the outer query. [S1]
SELECT employee_name, department, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);Read it from the inside out: SELECT AVG(salary) FROM employees calculates the overall average. The outer WHERE keeps employees whose salary is greater than that average. This is an illustrative SQLite example, not data from an actual workplace.
Can you show the intermediate average and which rows would come back?
Assume this small, invented employees table:
employee_name | department | salary
Ari | Sales | 50000
Bea | Support | 60000
Cam | Sales | 70000
Dev | Support | 90000
Eli | Sales | 110000The inner calculation is:
SELECT AVG(salary) FROM employees;For these five example rows, their salaries total 380000; divided by five, the average is 76000. You can think of the outer filter as WHERE salary > 76000. The final result is:
employee_name | department | salary
Dev | Support | 90000
Eli | Sales | 110000The comparison is strict: someone earning exactly 76000 would not pass >. If you want to include a salary equal to the average, use >= instead. These values are just a worked example; replace the table and column names with yours.
I also have departments. Does this calculate a separate average for each department?
No. As written, the inner query reads all rows in employees, so it calculates one overall average. The outer query then tests each row against that one value; the department column is only displayed, not used to calculate the threshold.
If your goal is specifically to compare each employee with their own department’s average, that is a different task and needs a department-specific calculation. Don’t assume this overall-average query does that. First decide which threshold you mean: one average across the whole table, or a separate average per department. The example here answers the first question only.
What should I check if I adapt this query and get unexpected results?
Check that the inner query is returning the aggregate you intend and that it refers to the same population you want to compare against. Confirm your table and column names, and choose > or >= deliberately. Also check whether you mean an overall threshold or a per-group threshold; adding department names to the displayed results does not change the inner query’s calculation.
For the shown SQLite pattern, the inner SELECT is scalar and supplies its value to the outer comparison. [S1] The cited SQLite material supports this pattern, but it does not establish that every database uses identical syntax or behavior. If you are using another database, consult its documentation. To inspect the threshold separately, run the inner aggregate query on its own, then compare that result with the rows returned by the full query.
A practical checklist
Confirm the threshold is the overall average, not a separate average per department.
Run
SELECT AVG(salary) FROM employees;by itself to inspect the comparison value.Use the same employee table for the aggregate and the outer filter if both should cover the same rows.
Choose
>to exclude values equal to the average, or>=to include them.Replace the illustrative table and column names with the names in your database.
A few more useful answers
Why use a subquery instead of typing the average into the WHERE clause?
The subquery calculates the threshold from the table rather than requiring you to enter a fixed number. That makes the comparison follow the aggregate produced by the inner query.
Does this query return employees earning exactly the average?
No. The shown > condition excludes equality. Use >= if equal-to-average salaries should also be returned.
Does the example prove this syntax works in every SQL database?
No. The cited evidence describes SQLite’s scalar-subquery behavior and illustrates this pattern in SQLite; it does not establish behavior for every database system. [S1]
Sources
References used for this guide.
- [S1] SQLite SQL Language Expressions: Scalar Subqueries sqlite.org · Checked