Subqueries Inside SELECT Lists

A SELECT list can contain columns, constants, expressions built from functions and operators, aggregate functions, scalar subqueries and window functions. The useful thing about a scalar subquery is that it lets you add a value to each row that does not exist in that row's table, without changing the row count or the row grain.

That last point is the reason it earns its place. If you write the same thing as a JOIN with a GROUP BY, the query collapses to one row per group. If you write it as a scalar subquery, the outer rows survive intact and each one gains an extra column. When you are enriching a list of rows with context — this customer's total, the customer's rank, the average for their peer group — that is exactly what you want.

Three constraints. The subquery must return exactly one row and one column, or the query fails with a "subquery returns more than one row" error. It must be scalar, so a subquery selecting several columns is rejected here even though the same subquery would be fine in a FROM clause. And if the subquery returns no rows, the value is NULL rather than an error — which is convenient, but it means you have to decide what NULL should display as.

The alias on the subquery's result is not optional in practice. Without it the output column gets an engine-generated name like (SELECT 1), which is unpleasant in a report and breaks any client that reads the result by column name.

A correlated scalar subquery with a COUNT inside it. The count is guaranteed to return a single value per row, so the query cannot fail with a multiple-rows error.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper'),
  (3, 'Alan Turing');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25);

SELECT c.id,
       c.name,
       (SELECT COUNT(*)
        FROM orders AS o
        WHERE o.customer_id = c.id) AS order_count
FROM customers AS c
ORDER BY c.id;

Three rows come back: Ada Lovelace with 2 orders, Grace Hopper with 1 and Alan Turing with 0. The count of zero is correct rather than NULL, because COUNT over an empty set returns 0 and not NULL.

Note the difference from a SUM subquery, which would give NULL for Alan. COUNT is defined to return a number, so the no-rows case gives you 0; SUM is defined to return a total, and the total of nothing is unknown, so it gives NULL. That inconsistency between the two aggregates is the thing to remember when you build a report this way.

The cost is the concern. This subquery is correlated, so it runs once per customer — three executions here. On a table with a million customers, that is a million separate count queries, and each one has to find the customer's orders. An index on orders(customer_id) makes each execution cheap, but the million executions still dominate. The scalable version pre-aggregates in a derived table and left joins:

the derived table groups the orders by customer_id and counts them once per group, then the outer query left joins that to customers and coalesces the missing counts to zero.

That version computes the count once per customer who has orders, rather than once per customer, which is a very different number when most customers have no orders at all. It is also easier to extend — adding a second aggregate column means one more expression in the derived table rather than another correlated subquery.

This is a classic share-of-total calculation. The denominator is the customer's total, so the subquery is correlated and has to return one number per outer row.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2, 610.00),
  (4, 2,  75.25);

SELECT o.id AS order_id,
       c.name,
       o.amount,
       (SELECT SUM(o2.amount)
        FROM orders AS o2
        WHERE o2.customer_id = o.customer_id) AS customer_total,
       ROUND(100.0 * o.amount /
             (SELECT SUM(o3.amount)
              FROM orders AS o3
              WHERE o3.customer_id = o.customer_id), 1) AS pct_of_customer
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.id;

Four rows come back. Order 1 is 120.00 out of Ada's 460.50, which is 26.1 per cent. Order 2 is 340.50 out of 460.50, which is 73.9 per cent. Orders 3 and 4 belong to Grace, whose total is 685.25: 610.00 is 89.0 per cent and 75.25 is 11.0 per cent. Each customer's two orders add up to 100 per cent, which is the check that the arithmetic is right.

Two implementation details. The multiplication is written as 100.0 * o.amount rather than o.amount * 100 deliberately: in SQLite, integer division truncates, so multiplying first by a float keeps the division exact. And the ROUND to one decimal place is a display decision — the underlying percentage has far more digits.

The obvious improvement is to compute the customer total once instead of twice per row, which a window function does in a single pass. SUM(o.amount) OVER (PARTITION BY o.customer_id) gives every row its customer's total, and the percentage is then just o.amount divided by that value — no subquery, no repeated scan, and it reads better. The subquery form is still worth knowing, because the window function requires a recent SQLite while the subquery works everywhere.

One edge case the window-function version handles more cleanly: if a customer's total were zero, the division would produce NULL in the window version and an error in some engines for the subquery version. Guard the denominator explicitly when zero totals are possible.