Adding Subqueries to the SELECT Clause
You can add a subquery to a SELECT clause as a column expression in the SELECT list. The subquery must return a scalar (single) value for each row returned by the outer query.
Query:-
SELECT
SalesOrderNumber,
SubTotal,
OrderDate,
(
SELECT SUM(OrderQty)
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 43659
) AS TotalQuantity
FROM
Sales.SalesOrderHeader
WHERE
SalesOrderID = 43659;
