Scalar and IN subqueries
Some questions need the answer to another question first, such as which makes are above average. A subquery lets one query use the result of another.
How it works
A subquery is a query inside a query. A scalar subquery returns one value that you use like any number. An IN subquery returns a list of values to test against. WITH gives a query a name, so you can use its result more than once.
WITH name AS (SELECT ...)defines a named result that you can read like a table further down the query.- A scalar subquery such as
(SELECT AVG(test_count) FROM per_make)returns one column and at most one row. Zero rows givesNULL, and more than one row fails when the query runs. value IN (SELECT column FROM ...)isTRUEwhen the value appears in the list that the subquery returns.- A subquery that does not refer to the outer query gives the same result for every outer row.
Common mistake
A scalar subquery that returns more than one row fails when the query runs. It does not fail when you write it. If you meant a single value, aggregate inside the subquery.
Worked example
WITH per_result AS (
SELECT test_result, COUNT(*) AS test_count
FROM `learn.mot.fact_mot_test`
WHERE completed_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY test_result
)
SELECT test_result, test_count,
ROUND(test_count / (SELECT SUM(test_count) FROM per_result), 3) AS share
FROM per_result
ORDER BY test_resultper_result has one row per test result. The scalar subquery adds those counts into a single number.
Each row divides its own count by that one total, which gives its share of all tests.