As part of the OLAPOperations effort, we'd like to add support for the GROUPING operation.

Rationale

The GROUPING operation is useful for identifying which rows in a result set are grouped by which columns. This really only matters when the result set contains multiple different groupings, as is the case with the CUBE, ROLLUP, and GROUPING SET features.

The result of the GROUPING operation is 1 if the values in this row are the results of aggregating over (possibly) multiple values of that column, and 0 if they are not.

Syntax

In the SQL 2003 standard, the <set function specification> has the following grammar:

<set function specification> ::=
    <aggregate function>
  | <grouping operation>

<grouping operation> ::=
    GROUPING <left paren> <column reference>
    [ { <comma> <column reference> }... ] <right paren>

A GROUPING operation can specify one or more column references as its arguments.

  • SQL Feature T431 specifies GROUPING operations of a single argument
  • SQL Feature T433 specifies GROUPING operations of multiple arguments

When a GROUPING operation is specified, each column reference argument must specify a grouping column (that is, a column that is used in the GROUP BY clause).

Example

SELECT department, location, SUM(salary) AS TOTAL_SAL,
   GROUPING(department) AS GD, GROUPING(location) AS GL
FROM employees
GROUP BY ROLLUP(department, location)

might return a table like:

DEPARTMENT  LOCATION  TOTAL_SAL  GD  GL
----------  --------  ---------  --  --
    10          1       1000     0   0
    10          2       5000     0   0
    20          1       2000     0   0
    10         null     6000     0   1
    20         null     2000     0   1
   null        null     8000     1   1

(Does the above table make sense? I'm not sure I'm understanding the GROUPING operation correctly.)

  • No labels