Syntax
GROUPING distinguishes superaggregate rows from regular grouped rows. GROUP BY extensions such as ROLLUP and CUBE produce superaggregate rows where the set of all values is represented by null. Using the GROUPING function, you can distinguish a null representing the set of all values in a superaggregate row from a null in a regular row.
i can't translate it now,maybe do it in the future.
The expr in the GROUPING function must match one of the expressions in the GROUP BY clause. The function returns a value of 1 if the value of expr in the row is a null representing the set of all values. Otherwise, it returns zero. The data type of the value returned by the GROUPING function is Oracle NUMBER. Refer to theSELECT group_by_clause for a discussion of these terms.
Grouping 中的表达式必须与group by中表达式匹配。如过在行中表达式的值是null(代表列的所有值)，则返回1.否则返回0.返回类型为number.
Examples
Conn hr/hr
SQL> desc employees
名称 是否为空? 类型
----------------------------------------- -------- --------------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
1、在ROLLUP中对单列使用GROUPING()
Select department_id,sum(salary)
From employees
Group by rollup(department_id)
Order by department_id;
DEPARTMENT_ID SUM(SALARY)
------------- -----------
10 4400
20 19000
30 24900
40 6500
50 156400
60 28800
70 10000
80 304500
90 58000
100 51608
110 20308
DEPARTMENT_ID SUM(SALARY)
------------- -----------
7000
691416
来看看加上grouping()之后的结果
Select grouping(department_id),sum(salary)
From employees
Group by rollup(department_id)
Order by department_id;
GROUPING(DEPARTMENT_ID) SUM(SALARY)
----------------------- -----------
0 4400
0 19000
0 24900
0 6500
0 156400
0 28800
0 10000
0 304500
0 58000
0 51608
0 20308
GROUPING(DEPARTMENT_ID) SUM(SALARY)
----------------------- -----------
0 7000
1 691416
可以看到原来departement_id为空的现在为1（此时就返回了所有部门的salary），有数值的现在为0.（倒数第二行不同是因为有位雇员此时该derptment_id为空，可以暂不管它）。
2、用case转换grouping（）的返回值
0,1和毕竟只是返回值，不够人性化。可以用case来转换为有意义的值。
select
case grouping(department_id)
When 1 then 'all departments'
else to_char(department_id)
End as dept,
Sum(salary)
From employees
Group by rollup(department_id)
Order by department_id;
DEPT SUM(SALARY)
---------------------------------------- -----------
10 4400
20 19000
30 24900
40 6500
50 156400
60 28800
70 10000
80 304500
90 58000
100 51608
110 20308
DEPT SUM(SALARY)
---------------------------------------- -----------
7000
all departments 691416
3、使用case和grouping()转换多个列的值
Select
Case grouping(department_id)
When 1 then 'all department'
Else to_char(department_id)
End as dept,
Case grouping(job_id)
When 1 then 'all jobs'
Else to_char(job_id)
End as jobs,
Sum(salary)
From employees
Group by rollup(department_id,job_id)
Order by department_id,job_id;
DEPT JOBS SUM(SALARY)
---------------------------------------- ---------- -----------
10 AD_ASST 4400
10 all jobs 4400
20 MK_MAN 13000
20 MK_REP 6000
20 all jobs 19000
30 PU_CLERK 13900
30 PU_MAN 11000
30 all jobs 24900
40 HR_REP 6500
40 all jobs 6500
50 SH_CLERK 64300
DEPT JOBS SUM(SALARY)
---------------------------------------- ---------- -----------
50 ST_CLERK 55700
50 ST_MAN 36400
50 all jobs 156400
60 IT_PROG 28800
60 all jobs 28800
70 PR_REP 10000
70 all jobs 10000
。。。。。。。。。。。。。。。。。。。。
90 AD_PRES 24000
DEPT JOBS SUM(SALARY)
---------------------------------------- ---------- -----------
90 AD_VP 34000
90 all jobs 58000
100 FI_ACCOUNT 39600
100 FI_MGR 12008
100 all jobs 51608
110 AC_ACCOUNT 8300
110 AC_MGR 12008
110 all jobs 20308
SA_REP 7000
all jobs 7000
all department all jobs 691416
已选择33行。
可以看到每个部门数据结束后，都有个关于该dept所有job的salary总结。这其实就是rollup的功能（ROLLUP，是GROUP BY子句的一种扩展，可以为每个分组返回小计记录以及为所有分组返回总计记录。）
4.cube与grouping()的结合使用
CUBE，也是GROUP BY子句的一种扩展，可以返回每一个列组合的小计记录，同时在末尾加上总计记录。
Select
Case grouping(department_id)
When 1 then 'all department'
Else to_char(department_id)
End as dept,
Case grouping(job_id)
When 1 then 'all jobs'
Else to_char(job_id)
End as jobs,
Sum(salary)
From employees
Group by cube(department_id,job_id)
Order by department_id,job_id;
。。。。。。。。。。。。。
50 all jobs 156400
60 IT_PROG 28800
60 all jobs 28800
70 PR_REP 10000
70 all jobs 10000
80 SA_MAN 61000
80 SA_REP 243500
80 all jobs 304500
90 AD_PRES 24000
DEPT JOBS SUM(SALARY)
---------------------------------------- ---------- -----------
90 AD_VP 34000
90 all jobs 58000
100 FI_ACCOUNT 39600
100 FI_MGR 12008
100 all jobs 51608
110 AC_ACCOUNT 8300
110 AC_MGR 12008
110 all jobs 20308
all department AC_ACCOUNT 8300
all department AC_MGR 12008
all department AD_ASST 4400
DEPT JOBS SUM(SALARY)
---------------------------------------- ---------- -----------
all department AD_PRES 24000
all department AD_VP 34000
all department FI_ACCOUNT 39600
all department FI_MGR 12008
all department HR_REP 6500
all department IT_PROG 28800
all department MK_MAN 13000
all department MK_REP 6000
all department PR_REP 10000
all department PU_CLERK 13900
all department PU_MAN 11000
DEPT JOBS SUM(SALARY)
---------------------------------------- ---------- -----------
all department SA_MAN 61000
SA_REP 7000
all department SA_REP 250500
all department SH_CLERK 64300
all department ST_CLERK 55700
all department ST_MAN 36400
all jobs 7000
all department all jobs 691416
已选择52行。
可以看到，cube不仅实现了rollup的功能，还对每个jobs进行了总结。
DECODE(GROUPING(department_name), 1, 'ALL DEPARTMENTS', department_name) AS department,
DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job,
COUNT(*) "Total Empl",
AVG(salary) * 12 "Average Sal"
FROM employees e, departments d
WHERE d.department_id = e.department_id
GROUP BY ROLLUP (department_name, job_id) ORDER BY department, job;