The GROUP_ID function uniquely identifies a group of rows that has been created by a GROUP BY clause in the query result set.
The GROUP BY extensions (such as CUBE) allow complex result sets that can include duplicate groupings. The GROUP_ID function allows you to distinguish among duplicate groupings.
If there are multiple sets of rows calculated for a given level, GROUP_ID assigns the value of 0 to all the rows in the first set. All other sets of duplicate rows for a particular grouping are assigned higher values, starting with 1.
GROUP_ID thus helps you filter out duplicate groupings from the result set. For example, you can filter out duplicate groupings by adding a HAVING clause condition GROUP_ID()=0 to the query.
GROUP_ID cannot be used in a WHERE clause, join condition, inside an aggregate function, or in the definition of a grouping attribute.
GROUP_ID() AS alias
Note that the function does not accept any parameters.
DEFINE r AS SELECT DimReseller_AnnualRevenue AS Revenue, DimReseller_AnnualSales AS Sales, DimReseller_OrderMonth AS OrderMonth; RETURN results AS SELECT COUNT(1) AS COUNT, GROUP_ID() AS gid, GROUPING(Revenue) AS grouping_Revenue, GROUPING(Sales) AS grouping_Sales, GROUPING(OrderMonth) AS grouping_OrderMonth FROM r GROUP BY OrderMonth, ROLLUP(Revenue,Sales)