Beginner

517. JOIN with Aggregate Subquery

Learn how to JOIN a table with a subquery that has already calculated aggregate results.

📘 Lesson 517 of 728 ⏱ 14 min 🎯 Advanced

An aggregate subquery can calculate grouped results first and then be joined to another table to add descriptive information.

Pre-aggregating data can simplify complex queries and sometimes prevent unwanted row multiplication.

Calculate total sales per customer and then join those totals to customer details.

SQL 
SELECT
    c.CustomerName,
    s.TotalSales
FROM Customers c
INNER JOIN (
    SELECT
        CustomerID,
        SUM(OrderAmount) AS TotalSales
    FROM Orders
    GROUP BY CustomerID
) s
    ON c.CustomerID = s.CustomerID;

Used in sales analytics, customer reporting, financial summaries, and dashboard datasets.

  • The subquery can aggregate data before the JOIN.
  • Pre-aggregation can reduce row multiplication.
  • The derived result needs a relationship key.
  • This pattern is useful for summary reporting.
  • Always validate the aggregation level.
Why aggregate before a JOIN?

Pre-aggregation can reduce the number of rows being joined and help prevent incorrect totals caused by row multiplication.

What is joined in this pattern?

The main table is joined to the aggregated result produced by the subquery.

Have you finished this lesson?

Introduction to SQL – All Lessons