SQL
Calculating Aggregates with Scalar Subqueries
Learn to use scalar subqueries in the SELECT clause to fetch a single aggregated value for each row, such as calculating total orders per customer efficiently.
SELECT
c.customer_id,
c.customer_name,
(SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) as total_orders,
(SELECT SUM(oi.quantity * oi.price_per_unit) FROM orders o JOIN order_items oi ON o.order_id = oi.order_id WHERE o.customer_id = c.customer_id) as total_spent
FROM
customers c;
How it works: This query uses two scalar subqueries in the `SELECT` clause to enrich the customer data. The first subquery calculates the total number of orders for each customer, and the second calculates the total amount spent. Each subquery executes for every row in the outer query, returning a single (scalar) value that is then displayed as a new column. This pattern is useful for adding summary statistics directly alongside detail data.