GROUPING

Used in conjunction with grouping-sets and super-groups, the GROUPING aggregate function returns a value that indicates whether a row returned in a GROUP BY answer set is a row generated by a grouping set that excludes the column represented by expression.

expression: The argument values can be any built-in data type, but must be an item of a GROUP BY clause.

The data type of the result is a small integer. It is set to one of the following values:

The value of expression in the returned row is a null value, and the row was generated by the super-group. This generated row can be used to provide subtotal values for the GROUP BY expression.

The value is other than the above.

Example

The following query:

     SELECT SALES_DATE, SALES_PERSON,
           SUM(SALES) AS UNITS_SOLD,
           GROUPING(SALES_DATE) AS DATE_GROUP,
           GROUPING(SALES_PERSON) AS SALES_GROUP
     FROM SALES
     GROUP BY CUBE( SALES_DATE, SALES_PERSON)
     ORDER BY SALES_DATE, SALES_PERSON

Results in:

SALES_DATE SALES_PERSON UNITS_SOLD DATE_GROUP SALES_GROUP
---------- ------------ ---------- ---------- -----------
12/31/1995 GOUNOT                1          0           0
12/31/1995 LEE                   6          0           0
12/31/1995 LUCCHESSI             1          0           0
12/31/1995 -                     8          0           1
03/29/1996 GOUNOT               11          0           0
03/29/1996 LEE                  12          0           0
03/29/1996 LUCCHESSI             4          0           0
03/29/1996 -                    27          0           1
03/30/1996 GOUNOT               21          0           0
03/30/1996 LEE                  21          0           0
03/30/1996 LUCCHESSI             4          0           0
03/30/1996 -                    46          0           1
03/31/1996 GOUNOT                3          0           0
03/31/1996 LEE                  27          0           0
03/31/1996 LUCCHESSI             1          0           0
03/31/1996 -                    31          0           1
04/01/1996 GOUNOT               14          0           0
04/01/1996 LEE                  25          0           0
04/01/1996 LUCCHESSI             4          0           0
04/01/1996 -                    43          0           1
-          GOUNOT               50          1           0
-          LEE                  91          1           0
-          LUCCHESSI            14          1           0
-          -                   155          1           1

An application can recognize a SALES_DATE subtotal row by the fact that the value of DATE_GROUP is 0 and the value of SALES_GROUP is 1. A SALES_PERSON subtotal row can be recognized by the fact that the value of DATE_GROUP is 1 and the value of SALES_GROUP is 0. A grand total row can be recognized by the fact that the value of both DATE_GROUP and SALES_GROUP is 1.