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.
The data type of the result is a small integer. It is set to one of the following values:
| 1 | 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. |
| 0 | The value is other than the above. |
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 1An 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.