Scalar subquery in SQL

A subquery that returns one value can stand in for a column or a constant.

SELECT
    o.id,
    o.total,
    (
        SELECT COUNT(*)
        FROM order_items AS i
        WHERE i.order_id = o.id
    ) AS item_count
FROM orders AS o
WHERE o.total > (
    SELECT AVG(total)
    FROM orders
);

How it works

In the SELECT list it is evaluated per outer row.
Compared against an aggregate it makes a relative filter.
Keep it correlated by referencing the outer alias.

Keywords and builtins used here

AS
AVG
COUNT
FROM
SELECT
WHERE

The run, in numbers

Lines: 13
Characters to type: 169
Tokens: 49
Three-star pace: 90 tpm

At the three-star pace of 90 tokens a minute, this run takes about 33 seconds.

Type this snippet

Step 2 of 3 in Subqueries, step 2 of 24 in Analytics & reporting.

← Previous Next →