GROUPING_ID function

The GROUPING_ID function computes the GROUP BY level of a particular row.

The GROUPING_ID function returns a single number that enables you to determine the exact GROUP BY level. For each row, GROUPING_ID takes the set of 1's and 0's that would be generated if you used the appropriate GROUPING functions and concatenated them, forming a bit vector. The bit vector is treated as a binary number, and the number's base-10 value is returned by the GROUPING_ID function.

For example, if you group with the expression CUBE(a, b), the possible values are:

Aggregation Level	Bit Vector	GROUPING_ID
a,b	0 0	0
a	0 1	1
b	1 0	2
Grand Total	1 1	3

GROUPING_ID syntax

The GROUPING_ID syntax is:

GROUPING_ID(attributeList)

where attributeList is a single attribute or a comma-separated list of 1-63 attributes.

GROUPING_ID example

DEFINE r AS SELECT
  DimReseller_AnnualRevenue AS Revenue,
  DimReseller_AnnualSales AS Sales;

RETURN results AS SELECT
  COUNT(1) AS COUNT,
  GROUPING_ID(Revenue) AS gid_Revenue,
  GROUPING_ID(Sales) AS gid_Sales
FROM r
GROUP BY CUBE(Revenue,Sales)