GROUPING function

GROUPING indicates whether a specified attribute expression in a GROUP BY list is aggregated.

The use of ROLLUP and CUBE can result in two challenging problems:

How can you programmatically determine which result set rows are subtotals, and how do you find the exact level of aggregation for a given subtotal? You often need to use subtotals in calculations such as percent-of-totals, so you need an easy way to determine which rows are the subtotals.
What happens if query results contain both stored NULL values and NULL values created by a ROLLUP or CUBE? How can you differentiate between the two?

The GROUPING function can handle these problems.

GROUPING is used to distinguish the NULL values that are returned by ROLLUP, CUBE, or GROUPING SETS from standard null values. The NULL returned as the result of a ROLLUP, CUBE, or GROUPING SETS operation is a special use of NULL. This acts as a column placeholder in the result set and means all.

GROUPING returns TRUE when it encounters a NULL value created by a ROLLUP, CUBE, or GROUPING SETS operation. That is, if the NULL indicates the row is a subtotal, GROUPING returns TRUE. Any other type of value, including a stored NULL, returns FALSE.

GROUPING thus lets you programmatically determine which result set rows are subtotals, and helps you find the exact level of aggregation for a given subtotal.

GROUPING syntax

The GROUPING syntax is:

GROUPING(attribute)

where attribute is a single attribute.

GROUPING example

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

RETURN results AS SELECT
  COUNT(1) AS COUNT,
  GROUPING(Revenue) AS grouping_Revenue,
  GROUPING(Sales) AS grouping_Sales,
  GROUPING(OrderMonth) AS grouping_OrderMonth
FROM r
GROUP BY
  GROUPING SETS (
    ROLLUP( 
      (Revenue),
      (Sales),
      (OrderMonth)
    )
  )