SQL> ed
Wrote file afiedt.buf

  1  select
  2   ename
  3  ,decode(grouping_id(ename),0,deptno) deptno
  4  ,decode(grouping_id(ename),0,job) job
  5  ,decode(grouping_id(ename),0,sum(sal)) sal
  6  ,case when grouping_id(job)=1 then 'Dept '||to_char(deptno)||':'||to_char(sum(sal))
  7            when grouping_id(ename)=1 then 'Job '||job||':'||to_char(sum(sal))
  8            end  sum_line
  9  from emp
 10  group by grouping sets ((ename,deptno,job),(deptno,job),deptno)
 11* having grouping_id(ename,deptno,job)<>4 or count(*)>1
SQL> /

ENAME          DEPTNO JOB              SAL SUM_LINE
---------- ---------- --------- ---------- --------------------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
                                           Dept 10:8750
ADAMS              20 CLERK           1100
SMITH              20 CLERK            800
                                           Job CLERK:1900
FORD               20 ANALYST         3000
SCOTT              20 ANALYST         3000
                                           Job ANALYST:6000
JONES              20 MANAGER         2975
                                           Dept 20:10875
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850
WARD               30 SALESMAN        1250
ALLEN              30 SALESMAN        1600
MARTIN             30 SALESMAN        1250
TURNER             30 SALESMAN        1500
                                           Job SALESMAN:5600
                                           Dept 30:9400

20 rows selected.

SQL>