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