The GROUPING_ID() function returns the decimal equivalent of
the GROUPING bit vector.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- from ww w .j ava2s.com
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
2 deptno, job,
3 GROUPING(deptno) AS Department_GRP,
4 GROUPING(job) AS JOB_GRP,
5 GROUPING_ID(deptno, job) AS grp_id,
6 SUM(sal)
7 FROM emp
8 GROUP BY CUBE(deptno, job)
9 ORDER BY deptno, job;
DEPTNO JOB DEPARTMENT_GRP JOB_GRP GRP_ID SUM(SAL)
---------- --------- -------------- ---------- ---------- ----------
10 MANAGER 0 0 0 2850
10 PRESIDENT 0 0 0 3000
10 0 1 1 5850
20 ANALYST 0 0 0 3000
20 CLERK 0 0 0 2300
20 MANAGER 0 0 0 2975
20 0 1 1 8275
30 MANAGER 0 0 0 2850
30 SALESMAN 0 0 0 5600
30 0 1 1 8450
ANALYST 1 0 2 3000
CLERK 1 0 2 2300
MANAGER 1 0 2 8675
PRESIDENT 1 0 2 3000
SALESMAN 1 0 2 5600
1 1 3 22575
16 rows selected.
SQL>
In the following example, GROUPING_ID() filters rows by using a HAVING clause.
The HAVING clause excludes rows that don't contain a subtotal or total by checking if GROUPING_ID() returns a value greater than 0.
SQL> SELECT-- w w w.ja v a2 s.c o m
2 deptno, job,
3 GROUPING_ID(deptno, job) AS grp_id,
4 SUM(sal)
5 FROM emp
6 GROUP BY CUBE(deptno, job)
7 HAVING GROUPING_ID(deptno, job) > 0
8 ORDER BY deptno, job;
DEPTNO JOB GRP_ID SUM(SAL)
---------- --------- ---------- ----------
10 1 5850
20 1 8275
30 1 8450
ANALYST 2 3000
CLERK 2 2300
MANAGER 2 8675
PRESIDENT 2 3000
SALESMAN 2 5600
3 22575
9 rows selected.
SQL>