SQL Subqueries in SELECT and HAVING

In Lesson 10.1 · Scalar subqueries, the subquery sat in WHERE, comparing each product with the average price. A scalar subquery returns one value, so it can go anywhere a single value can. Two places come up constantly in reports: the SELECT list, to show a total beside each row, and HAVING, to filter groups against it.

A total beside every group

What share of all units sold does each category account for? The grouped query gives each category's units. The total across every category comes from a subquery in the SELECT list:

select
  c.category_name,
  sum(oi.quantity) as units,
  round(100.0 * sum(oi.quantity) / (
    select sum(quantity)
    from order_items
  ), 1) as pct_of_units
from order_items as oi
join products as p on p.product_id = oi.product_id
join categories as c on c.category_id = p.category_id
group by c.category_name
order by units desc, c.category_name

The subquery has no GROUP BY and doesn't refer to the outer query, so it returns the same number, 87, on every row. Houseplants sold 23 of those units, 26.4%. A subquery like this, one that doesn't depend on the outer row, runs once for the whole query, not once per group.

The 100.0 matters. Units are whole numbers, and in PostgreSQL and SQL Server an integer divided by an integer drops the fraction, so 100 * 23 / 87 comes out as 26. DuckDB's / always keeps the fraction, but writing 100.0 keeps the query correct everywhere.

Filtering groups against the total

Which categories average more than the store as a whole? The comparison is between an aggregate, each category's avg(price), and one number, the store-wide average. Comparisons on aggregates belong in HAVING (Lesson 4.4), and the subquery supplies the number:

select c.category_name, round(avg(p.price), 2) as avg_price
from products as p
join categories as c on c.category_id = p.category_id
group by c.category_name
having avg(p.price) > (
  select avg(price)
  from products
)
order by avg_price desc

Three of the eight categories clear the store average of 14.36. Put the same condition in WHERE instead and DuckDB stops with Binder Error: WHERE clause cannot contain aggregates!, because WHERE filters rows before they're grouped, so there's no category average yet to compare. The subquery itself is fine in either clause. It's the avg(p.price) on the left that needs HAVING.

When the subquery returns nothing, or too much

The two rules from Lesson 10.1 still apply. A subquery that matches no rows returns NULL, so a share calculated against it is NULL on every row, and a HAVING comparison against it keeps no groups. No error, just an empty or blank result. A subquery that returns more than one row stops the query with More than one row returned by a subquery. An aggregate with no GROUP BY, like the two above, always returns exactly one row, which is why it's the usual choice.

Writing the subquery once

The exercise below needs the store average three times: as a column, inside a difference, and in HAVING. Repeating a short subquery is normal, and every major database accepts it. When it grows, two later lessons offer a way to write it once: a common table expression (Lesson 11.5) names it at the top of the query, and avg(price) over (), a window function (Lesson 11.4), puts the overall average on every row with no subquery at all.