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.

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_result

per_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.

All BigQuery lessons