Subquery Factoring: Example The following statement creates the query names dept_costs and avg_cost for the initial query block containing a join, and then uses the query names in the body of the main query.
WITH
dept_costs AS (
SELECT department_name, SUM(salary) dept_total
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id
GROUP BY department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs)
SELECT * FROM dept_costs
WHERE dept_total >
(SELECT avg FROM avg_cost)
ORDER BY department_name