Using Aggregate Functions with ROLLUP

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));

INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);


SQL>
SQL> SELECT deptno, job, AVG(sal)
  2  FROM emp
  3  GROUP BY ROLLUP(deptno, job)
  4  ORDER BY deptno, job;

    DEPTNO JOB         AVG(SAL)
---------- --------- ----------
        10 MANAGER         2850
        10 PRESIDENT       3000
        10                 2925
        20 ANALYST         3000
        20 CLERK           1150
        20 MANAGER         2975
        20              2068.75
        30 MANAGER         2850
        30 SALESMAN        1400
        30                 1690
                     2052.27273

11 rows selected.

SQL>