Multiple CTEs

Source data — Pattern 9: Common Table Expressions (CTEs)

All queries run against the shared employees and departments tables (see schema.sql). This pattern introduces Common Table Expressions (CTEs). Topic 54 demonstrates a recursive CTE using the employee-manager hierarchy from the employees table.

---

What it does

Multiple CTEs can be declared in a single WITH clause and referenced together.

Problem

Calculate department salary summaries.

WITH dept_salary AS (
  SELECT department_id,SUM(salary) total_salary FROM employees GROUP BY department_id
), dept_count AS (
  SELECT department_id,COUNT(*) total_emp FROM employees GROUP BY department_id
)
SELECT * FROM dept_salary JOIN dept_count USING(department_id);

Result