The
Grouping function can be used with either the
cube or rollup operator. Using this
function we can find the basis on which the groups are formed and it’s subtotal.
It accepts a single column as a parameter and returns numeric values to
identify the column value in an aggregated row. It returns 1 if the column
contains a null value generated as part of a subtotal by a ROLLUP or CUBE
operation and zero if it is NOT NULL
Example
Syntax:
GROUPING (Column)
Examples Of Grouping Function:
SQL> select
deptno deptid,job JOB_ID,sum(sal),
grouping(deptno)
grp_dept,grouping(job)grp_job
from emp where deptno <20 group by rollup
(deptno,job);
DEPTID JOB_ID SUM(SAL)
GRP_DEPT GRP_JOB
----------
--------- ---------- ---------- ----------
10 CLERK 1300 0 0
10 MANAGER 2450 0 0
10 PRESIDENT 5000 0 0
10 8750 0 1
8750
1 1
SQL>
select deptno deptid,job JOB_ID,sum(sal),
grouping(deptno)
grp_dept,grouping(job)grp_job
from emp where deptno <30 group by rollup
(deptno,job);
DEPTID JOB_ID SUM(SAL)
GRP_DEPT GRP_JOB
----------
--------- ---------- ---------- ----------
10 CLERK 1300 0 0
10 MANAGER 2450 0 0
10 PRESIDENT 5000 0 0
10 8750 0 1
20 CLERK 1900 0 0
20 ANALYST 6000 0 0
20 MANAGER 2975 0 0
20 10875 0 1
19625 1 1
9
rows selected.
SQL>
select deptno deptid,job JOB_ID,sum(sal),
grouping(deptno)
grp_dept,grouping(job)grp_job
from emp where deptno <=30 group by rollup (deptno,job);
DEPTID JOB_ID SUM(SAL)
GRP_DEPT GRP_JOB
----------
--------- ---------- ---------- ----------
10 CLERK 1300 0 0
10 MANAGER 2450 0 0
10 PRESIDENT 5000 0 0
10 8750 0 1
20 CLERK 1900 0 0
20 ANALYST 6000 0 0
20 MANAGER 2975 0 0
20 10875 0 1
30 CLERK 950 0
0
30 MANAGER 2850 0 0
30 SALESMAN 5600 0 0
DEPTID JOB_ID SUM(SAL)
GRP_DEPT GRP_JOB
----------
--------- ---------- ---------- ----------
30 9400
0 1
29025 1 1
13
rows selected.
No comments:
Post a Comment