How do you use a subquery within the SELECT list to calculate a related aggregate value alongside each row of the outer query?

Intermediate

Answer

You place a correlated subquery directly inside the SELECT list, referencing the outer query's current row, which calculates and returns a related value, such as a customer's total number of orders, computed individually for each row of the outer result set.

SELECT c.Name,
  (SELECT COUNT(*) FROM Orders o WHERE o.CustomerId = c.CustomerId) AS OrderCount
FROM Customers c;

Real-world example A customer report shows each customer's name alongside their total number of orders, calculated using a subquery in the SELECT list that runs once for each customer row in the outer query.

Common follow-ups: Is this approach as efficient as using a JOIN with GROUP BY for the same result?;What happens if the subquery in the SELECT list returns more than one row?

Aggregate Functions & GROUP BY;Joins

All 7 Subqueries questions