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));
-- from ww w .j ava 2 s. co m
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>
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- from w w w . j a v a 2s . c om
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 CASE GROUPING(deptno)
3 WHEN 1 THEN 'All divisions'
4 ELSE 'Not'
5 END AS div,
6 SUM(sal)
7 FROM emp
8 GROUP BY ROLLUP(deptno)
9 ORDER BY deptno;
DIV SUM(SAL)
------------- ----------
Not 5850
Not 8275
Not 8450
All divisions 22575
SQL>
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- w ww . j av a2 s . c om
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 CASE GROUPING(deptno)
3 WHEN 1 THEN 'All divisions'
4 ELSE 'else'
5 END AS div,
6 CASE GROUPING(job)
7 WHEN 1 THEN 'All jobs'
8 ELSE 'elseJob'
9 END AS job,
10 SUM(sal)
11 FROM emp
12 GROUP BY ROLLUP(deptno, job)
13 ORDER BY deptno, job;
DIV JOB SUM(SAL)
------------- -------- ----------
else All jobs 5850
else elseJob 3000
else elseJob 2850
else All jobs 8275
else elseJob 3000
else elseJob 2300
else elseJob 2975
else All jobs 8450
else elseJob 2850
else elseJob 5600
All divisions All jobs 22575
11 rows selected.
SQL>
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- from w w w . ja va2 s . 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 CASE GROUPING(deptno)
3 WHEN 1 THEN 'All departments'
4 ELSE 'deptno'
5 END AS div,
6 CASE GROUPING(job)
7 WHEN 1 THEN 'All jobs'
8 ELSE 'job'
9 END AS job,
10 SUM(sal)
11 FROM emp
12 GROUP BY CUBE(deptno, job)
13 ORDER BY deptno, job;
DIV JOB SUM(SAL)
--------------- -------- ----------
deptno All jobs 5850
deptno job 3000
deptno job 2850
deptno All jobs 8275
deptno job 2975
deptno job 3000
deptno job 2300
deptno All jobs 8450
deptno job 2850
deptno job 5600
All departments All jobs 22575
All departments job 8675
All departments job 3000
All departments job 2300
All departments job 5600
All departments job 3000
16 rows selected.
SQL>
The GROUPING SETS clause gets the subtotal rows.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- w w w.ja va2 s . c om
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, job, SUM(sal)
2 FROM emp
3 GROUP BY GROUPING SETS(deptno, job)
4 ORDER BY deptno, job;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 5850
20 8275
30 8450
ANALYST 3000
CLERK 2300
MANAGER 8675
PRESIDENT 3000
SALESMAN 5600
8 rows selected.
SQL>