Beginner

669. JOIN with Aggregated CTE

Use a CTE to aggregate data before JOINing it with another dataset.

📘 Lesson 669 of 728 ⏱ 15 min 🎯 Advanced

An aggregated CTE summarizes records at a defined grain and exposes the result for use in the main JOIN query.

It improves readability and helps prevent row multiplication in complex reporting queries.

Calculate department salary statistics and JOIN them with department details.

SQL 
WITH DepartmentStats AS (
    SELECT
        DepartmentID,
        COUNT(*) AS EmployeeCount,
        AVG(Salary) AS AverageSalary,
        MAX(Salary) AS MaximumSalary
    FROM Employees
    GROUP BY DepartmentID
)
SELECT
    d.DepartmentName,
    ds.EmployeeCount,
    ds.AverageSalary,
    ds.MaximumSalary
FROM Departments d
LEFT JOIN DepartmentStats ds
    ON d.DepartmentID = ds.DepartmentID;

Used in HR dashboards, financial reports, MIS, and analytical SQL.

  • The CTE performs aggregation first.
  • The main query JOINs the summarized result.
  • The CTE grain should match the JOIN key.
  • This pattern improves query organization.
  • It can simplify complex reports.
Why aggregate inside a CTE before JOINing?

It creates a controlled summary at the required grain before the JOIN.

What should you verify before JOINing the CTE?

Verify that the CTE produces the expected number of rows per JOIN key.

Have you finished this lesson?

Introduction to SQL – All Lessons