GROUPING_ID

The GROUPING_ID function returns a number that shows the exact GROUP BY level of aggregation resulting from a ROLLUP or CUBE clause.

Note:

See "GROUP BY Clause" for details on ROLLUP and CUBE clauses.

The GROUPING_ID function takes the ordered list of grouping columns from the ROLLUP or CUBE as input and computes the grouping ID as follows:

  1. Applies the GROUPING function to each of the individual columns in the list. The result is a set of ones and zeros, where 1 represents a superaggregate generated by the ROLLUP or CUBE.

  2. Puts these ones and zeros in the same order as the order of the columns in its argument list to produce a bit vector.

  3. Converts this bit vector from a binary number into a decimal (base 10) number, which is returned as the grouping ID.

For instance, if you group with CUBE(department_id, job_id), the returned values are as shown in Table 4-1.

Table 4-1 GROUPING_ID Example for CUBE(department_id, job_id)

Aggregation Level Bit Vector GROUPING_ID

Normal grouping rows for department and job

0 0

0

Subtotal for department_id, aggregated at job_id

0 1

1

Subtotal for job_id, aggregated at department_id

1 0

2

Grand total

1 1

3

The GROUPING_ID function can be used in a query to filter rows so that only the summary rows are displayed. You can use the GROUPING_ID function in the HAVING clause to restrict output to only those rows that contain totals and subtotals. This can be accomplished when adding a comparison of the GROUPING_ID function results as greater than zero in the HAVING clause.

Syntax

The GROUPING_ID function is applicable only in a SELECT statement that contains the GROUP BY clause, a GROUPING function, and one of the following clauses: ROLLUP, CUBE or GROUPING SETS clauses. It can be used in the select list and HAVING clause of the SELECT query. 

GROUPING_ID(Expression [, Expression ]...)

Parameters

Parameter Description

Expression

Valid expression syntax. See Expressions.

Examples

The following example has the HAVING clause filter on the GROUPING_ID function, where the returned value is greater than zero. This excludes rows that do not contain either a subtotal or grand total. The following example shows the subtotals for the departments are identified with a group ID of 1, subtotals for the job ID with a group ID of 2 and the grand total with a group ID of 3: 

Command> SELECT department_id AS DEPT, job_id AS JOB, 
           GROUPING_ID(department_id, job_id) AS GRP_ID, 
           SUM(salary) AS SALARY 
         FROM emp_details_view 
         GROUP BY CUBE(department_id, job_id) 
         HAVING GROUPING_ID(department_id, job_id) > 0 
         ORDER BY department_id, job_id, grp_id ASC;
 
DEPT, JOB, GRP_ID, SALARY
< 10, <NULL>, 1, 4400 >
< 20, <NULL>, 1, 19000 >
< 30, <NULL>, 1, 24900 >
< 40, <NULL>, 1, 6500 >
< 50, <NULL>, 1, 156400 >
< 60, <NULL>, 1, 28800 >
< 70, <NULL>, 1, 10000 >
< 80, <NULL>, 1, 304500 >
< 90, <NULL>, 1, 58000 >
< 100, <NULL>, 1, 51600 >
< 110, <NULL>, 1, 20300 >
< <NULL>, AC_ACCOUNT, 2, 8300 >
< <NULL>, AC_MGR, 2, 12000 >
< <NULL>, AD_ASST, 2, 4400 >
< <NULL>, AD_PRES, 2, 24000 >
< <NULL>, AD_VP, 2, 34000 >
< <NULL>, FI_ACCOUNT, 2, 39600 >
< <NULL>, FI_MGR, 2, 12000 >
< <NULL>, HR_REP, 2, 6500 >
< <NULL>, IT_PROG, 2, 28800 >
< <NULL>, MK_MAN, 2, 13000 >
< <NULL>, MK_REP, 2, 6000 >
< <NULL>, PR_REP, 2, 10000 >
< <NULL>, PU_CLERK, 2, 13900 >
< <NULL>, PU_MAN, 2, 11000 >
< <NULL>, SA_MAN, 2, 61000 >
< <NULL>, SA_REP, 2, 243500 >
< <NULL>, SH_CLERK, 2, 64300 >
< <NULL>, ST_CLERK, 2, 55700 >
< <NULL>, ST_MAN, 2, 36400 >
< <NULL>, <NULL>, 3, 684400 >
31 rows found.