select /*+ gather_plan_statistics */
distinct dname, decode( d.deptno,
10, (select count(*) from scott.emp where  deptno = 10),
20, (select count(*) from scott.emp where deptno = 20),
30, (select count(*) from scott.emp where deptno = 30),
(select count(*) from scott.emp where deptno not in(10, 20, 30))
) dept_count
from (select distinct deptno from scott.emp) d
join scott.dept d2 on d2.deptno = d.deptno;

下面说一下, 新的一些 group by 扩展 (关于这些新功能, 在我的其他BLOG中有)

CUBE, GROUPING()

GROUP BY 局限性

LOB 列, 嵌套表或数组不能用做 GROUP BY 表达式的一部分

标量子查询表达式是不准许的 group by (select dname from scott.dept d2 where d2.dname = d.dname )  这是不准许的

如果group by 子句引用任何对象类型的列则查询不能并行化

07 高级分组