grouping sets

select deptno,empno,sum(sal) from emp

group by grouping sets((deptno,empno),deptno,null)

select deptno,empno,sum(sal) from emp
group by deptno,empno
union all
select deptno,null empno,sum(sal) from emp
group by deptno
union all
select null deptno,null empno,sum(sal) from emp
group by null
order by deptno,empno

group by cube/rollup:

http://blog.csdn.net/yfb8530/article/details/8722833