SQL Subquery: Compare Rows With a Calculated Benchmark

The practice task

A reading club wants books whose page count is above the catalogue average. Calculate the average from the same fictional catalogue, then return the qualifying title without hard-coding the benchmark.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE books(title TEXT, pages INTEGER);
INSERT INTO books VALUES ('Atlas',100),('Bridges',200),('Currents',300);

SELECT title, pages FROM books
WHERE pages > (SELECT AVG(pages) FROM books)
ORDER BY title;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The inner query produces 200.0, the average of 100, 200 and 300. The outer comparison keeps Currents because 300 exceeds that benchmark. Bridges equals the benchmark and therefore fails the strict greater-than condition.

Keeping the benchmark in a query makes it respond to changes in the catalogue. Adding another book can change both the average and the qualifying rows. Replacing the subquery with the literal 200 would freeze an assumption that may no longer be true.

This example is uncorrelated: the inner query does not refer to an outer-row column. A per-category comparison would need a different benchmark for each category, using correlation, a grouped join or a window calculation. Do not assume a global average answers a within-category question.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Check that a scalar subquery has one intended result.
• Decide which population belongs in the benchmark.
• Use IN or EXISTS for a membership task instead of pretending a rowset is a scalar.