GROUPING[_ID]
Purpose
With the use of this function you can distinguish between regular result rows and super-aggregate rows which are created in case of GROUPING SETS, CUBE or ROLLUP clauses.
Syntax
grouping::=
Usage Notes
- Each argument must be similar to an expression within the GROUP BY clause.
- In case of a single argument the result value is 0 if the corresponding grouping considers this expression, otherwise 1 (superaggregation).
- In case of multiple arguments the result value is a number whose binary representation is similar to GROUPING(
arg1),GROUPING(arg2),..., GROUPING(argn). For example, the following is true:
GROUPING(a,b,c) = 4xGROUPING(a) + 2xGROUPING(b) + 1xGROUPING(c) - For more information on GROUPING SETS, CUBE and ROLLUP, refer to the SELECT statement in the Query language (DQL) section.
Example
SELECT SUM(volume) revenue, y, m, DECODE(GROUPING(y,m),1,'yearly',3,'total',NULL) superaggregate FROM sales GROUP BY ROLLUP(y,m) ORDER BY y,revenue; REVENUE Y M SUPERAGGREGATE ------------------- -------- ---------- -------------- 1725.90 2010 December 1725.90 2010 yearly 735.88 2011 April 752.46 2011 February 842.32 2011 March 931.18 2011 January 3261.84 2011 yearly 4987.74 total