db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Update of "OLAPGroupingOperation" by BryanPendleton
Date Fri, 20 Jul 2007 20:18:48 GMT
Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by BryanPendleton:

New page:
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:

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

View raw message