with summary as

This is new with 9i. Sometimes you do a subselect and have to reuse the same query to slice the data differently. Check out the example. See how the summary query is reused.

with summary as
(select dname, sum(sal) as dept_total
from emp, dept
where emp.deptno = dept.deptno
group by dname)
select dname, dept_total
from summary
where dept_total >
(select sum(dept_total * 1/3
from summary)
order by dept_total desc;