Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
View PDF |
Syntax
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 datatype of the value returned by the GROUPING
function is Oracle NUMBER
. Please 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); DEPARTMENT JOB Total Empl Average Sal ------------------------------ ---------- ---------- ----------- Accounting AC_ACCOUNT 1 99600 Accounting AC_MGR 1 144000 Accounting All Jobs 2 121800 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 FI_ACCOUNT 5 95040 Finance FI_MGR 1 144000 Finance All Jobs 6 103200 . . .