GROUP WISE QUERY

select deptno,
decode(grouping(ename),
1, 'Total dept '||deptno||': '||count(*) over(partition by deptno),
deptno) dept,
decode(grouping(ename),
1, to_number(null),
row_number() over(partition by deptno order by ename)) rn,
ename
from emp
group by rollup(deptno,ename)
having grouping(deptno) = 0
order by deptno, rn
/