An aggregated subquery calculates grouped results first and exposes them as a derived dataset for the outer query.
This pattern provides precise control over the grain of the JOIN and helps avoid duplicate calculations.
Calculate order count and total sales per customer before joining to customer details.
SELECT
c.CustomerName,
s.OrderCount,
s.TotalSales
FROM Customers c
LEFT JOIN (
SELECT
CustomerID,
COUNT(OrderID) AS OrderCount,
SUM(OrderAmount) AS TotalSales
FROM Orders
GROUP BY CustomerID
) s
ON c.CustomerID = s.CustomerID;Used in dashboards, customer summaries, financial analysis, and reporting systems.
- The subquery creates one row per customer.
- Aggregation occurs before the JOIN.
- It controls result grain.
- LEFT JOIN can preserve customers without orders.
- Derived tables should have clear aliases.
Why use an aggregated subquery before JOIN?
It creates a summarized result at the required grain before combining it with the main table.
What problem does this pattern help prevent?
It can prevent row multiplication and incorrect aggregate calculations.