GROUPING

Syntax

Description of grouping.gif follows
Description of the illustration ''grouping.gif''

Purpose

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.

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 the SELECT group_by_clause for a discussion of these terms.

Examples

In the following example, which uses the sample tables hr.departments and hr.employees, if the GROUPING function returns 1 (indicating a superaggregate row rather than a regular row from the table), then the string "All Jobs" appears in the "JOB" column instead of the null that would otherwise appear:

SELECT 
 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;
DEPARTMENT JOB Total Empl Average Sal
------------------------------ ---------- ---------- -----------
ALL DEPARTMENTS All Jobs 106 77481.0566
Accounting AC_ACCOUNT 1 99600
Accounting AC_MGR 1 144096
Accounting All Jobs 2 121848
Administration AD_ASST 1 52800
Administration All Jobs 1 52800
Executive AD_PRES 1 288000
Executive AD_VP 2 204000
Executive All Jobs 3 232000
Finance All Jobs 6 103216
Finance FI_ACCOUNT 5 95040
. . .