Beginner

683. JOIN with Aggregated Subquery

Use an aggregated subquery to summarize data before JOINing it with another table.

📘 Lesson 683 of 728 ⏱ 15 min 🎯 Advanced

An aggregated subquery performs calculations such as SUM or COUNT before its result is JOINed to another dataset.

It helps maintain the correct result grain and prevents detail-level multiplication.

Show each department's employee count beside its department name.

SQL 
SELECT
    d.DepartmentName,
    x.EmployeeCount
FROM Departments d
LEFT JOIN (
    SELECT
        DepartmentID,
        COUNT(*) AS EmployeeCount
    FROM Employees
    GROUP BY DepartmentID
) x
    ON d.DepartmentID = x.DepartmentID;

Used in HR reports, financial MIS, operational dashboards, and management reporting.

  • Aggregation happens before the JOIN.
  • GROUP BY defines the subquery grain.
  • LEFT JOIN can preserve departments with no employees.
  • Pre-aggregation can prevent duplicate results.
  • Always verify the number of rows produced by the subquery.
Why use an aggregated subquery before JOIN?

To summarize detail records before combining them with another table.

What can happen without pre-aggregation?

Joining multiple detail tables directly can multiply rows and produce incorrect totals.

Have you finished this lesson?

Introduction to SQL – All Lessons