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.

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.)

OLAPGroupingOperation (last edited 2009-09-20 22:11:55 by localhost)