GROUPING
The GROUPING() function accepts a column and returns 0 or 1.
GROUPING() returns 1 when the column value is null.
GROUPING() returns 0 when the column value is not null.
GROUPING() is used only in queries that use ROLLUP or CUBE.
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> SELECT deptno, SUM(sal)
2 FROM emp
3 GROUP BY ROLLUP(deptno)
4 ORDER BY deptno;
DEPTNO SUM(SAL)
---------- ----------
10 5850
20 8275
30 8450
22575
SQL>
SQL>
SQL> SELECT GROUPING(deptno), deptno, SUM(sal)
2 FROM emp
3 GROUP BY ROLLUP(deptno)
4 ORDER BY deptno;
GROUPING(DEPTNO) DEPTNO SUM(SAL)
---------------- ---------- ----------
0 10 5850
0 20 8275
0 30 8450
1 22575
SQL>
SQL>