GROUPING_ID

Syntax

grouping_id::=

Text description of grouping_id

Purpose

The GROUPING_ID function returns a number corresponding to the GROUPING bit vector associated with a row. GROUPING_ID is applicable only in a SELECT statement that contains a GROUP BY extension, such as ROLLUP or CUBE, and a GROUPING function. In queries with many GROUP BY expressions, determining the GROUP BY level of a particular row requires many GROUPING functions, which leads to cumbersome SQL. GROUPING_ID is useful in these cases.

GROUPING_ID is functionally equivalent to taking the results of multiple GROUPING functions and concatenating them into a bit vector (a string of ones and zeros). By using GROUPING_ID you can avoid the need for multiple GROUPING functions and make row filtering conditions easier to express. Row filtering is easier with GROUPING_ID because the desired rows can be identified with a single condition of GROUPING_ID = n. The function is especially useful when storing multiple levels of aggregation in a single table.

Example

The following example shows how to extract grouping IDs from a query of the sample table sh.sales:

SELECT channel_id, promo_id, sum(amount_sold) s_sales,
   GROUPING(channel_id) gc,
   GROUPING(promo_id) gp,
   GROUPING_ID(channel_id, promo_id) gcp,
   GROUPING_ID(promo_id, channel_id) gpc
   FROM sales
   WHERE promo_id > 496
   GROUP BY CUBE(channel_id, promo_id);
 
C   PROMO_ID    S_SALES         GC         GP        GCP        GPC
- ---------- ---------- ---------- ---------- ---------- ----------
C        498   28024.25          0          0          0          0
C        499      25042          0          0          0          0
C              53066.25          0          1          1          2
I        498    54428.2          0          0          0          0
I        499   72725.25          0          0          0          0
I             127153.45          0          1          1          2
P        498   35801.75          0          0          0          0
P        499   21041.15          0          0          0          0
P               56842.9          0          1          1          2
S        498   95413.05          0          0          0          0
S        499   88706.75          0          0          0          0
S              184119.8          0          1          1          2
T        498       5147          0          0          0          0
T        499   16789.45          0          0          0          0
T              21936.45          0          1          1          2
         498  218814.25          1          0          2          1
         499   224304.6          1          0          2          1
              443118.85          1          1          3          3