Menu
CoddyTech

Scalar Subqueries

Part of the Connected Data section of Coddy's PostgreSQL journey. Lesson 6 of 25.

A parenthesized SELECT can supply a single value to an outer query. Such a scalar subquery must return one column and at most one row. An aggregate without GROUP BY is a useful way to produce one value. A scalar subquery returning no rows supplies NULL; more than one row is an error.

Input table orders:

idcustomer_idamountordered_onstatusmeta
111202025-01-02paid{"channel":"web","items":2,"shipping":{"city":"Haifa"}}
121302025-01-04paid{"channel":"app","items":3,"shipping":{"city":"Haifa"}}
132302025-02-01pending{"channel":"web","items":1}
143502025-02-03paid{"channel":"app","items":5,"shipping":{"city":"Eilat"}}
152102025-02-03paid{"channel":"web","items":2}
SELECT id, amount FROM orders WHERE amount > (SELECT AVG(amount) FROM orders) ORDER BY id;

The average amount is 28, so orders 12, 13 and 14 pass. The inner query computes the comparison value from the whole orders table. It does not need to be copied into each row by hand.

Use a scalar subquery when a condition depends on one value computed by another query.

challenge icon

Challenge

Medium

Return id and name for products priced above the average product price. Order by id.

The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.

Try it yourself

-- Write your SQL here.
quiz iconTest yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Connected Data

Practice on your own: PostgreSQL playground