SQL SERVER – Two Different Requirements for a Single Table Subquery

A subquery can compare rows with an aggregate from the same table. A junior DBA asked about this over lunch.

-- Run in WideWorldImporters.
-- Order lines whose individual price exceeds the overall line-price average:
SELECT OrderID, UnitPrice
FROM Sales.OrderLines
WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Sales.OrderLines);
-- The different requirement: orders whose average line price exceeds that average.
SELECT OrderID, AVG(UnitPrice) AS AverageLinePrice
FROM Sales.OrderLines
GROUP BY OrderID
HAVING AVG(UnitPrice) > (SELECT AVG(UnitPrice) FROM Sales.OrderLines);

We discussed this during a performance health check. My original query finds individual order lines above the overall average UnitPrice. An OrderID can appear repeatedly. That query doesn’t calculate each order’s average as the original problem requested.

GROUP BY and HAVING answer the per-order question. Both examples use unweighted line-price averages. A quantity-weighted business requirement needs a different expression. Handle a zero quantity total explicitly.

This subquery is uncorrelated because it has no outer-row reference. Subqueries can also appear in SELECT or FROM under statement-specific rules. The linked follow-up covers correlation.

Related reading

• SQL Server Performance Health Check
• SQL SERVER – How to Write Correlated Subquery?

A row above a global average is not an order with a high average, it is a different comparison.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.