SQL · Pattern 9 — Common Table Expressions (CTEs)
Multiple CTEs
Multiple CTEs can be declared in a single WITH clause and referenced together.
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
