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

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