GROUPING

GROUPING ( <field_expr> )

Returns a value of 1 when the row is added by the ROLLUP operator of the GROUP BY clause, or 0 when the row is not the result of ROLLUP.
GROUPING is used to distinguish the NULL values returned by ROLLUP from standard NULL values. The NULL returned as the result of a ROLLUP operation is a special use of NULL. It acts as a value placeholder in the result set and means "all".

Arguments:

<field_expr>

The GROUP BY field-expression checked for null values.

Return Type:

INTEGER

Remarks:

• The GROUPING aggregate function is allowed only when the GROUP BY clause contains the ROLLUP operator.
• Aggregate functions are allowed as field-expressions only in the SELECT, HAVING, and ORDER BY clauses.
• The arguments of an aggregate function can not reference other aggregate functions.
• The arguments of an aggregate function can not reference the following functions:
  • SEQUENCE
  • OUT_ROW_NUMBER

Examples:

A. GROUPING

The following query, on an IISW3C log file, returns the number of requests for each page on each day, and uses the ROLLUP operator to also display summary rows showing the number of requests for each day, and the total number of requests:

SELECT date, cs-uri-stem, COUNT(*), GROUPING(date) AS GDate, GROUPING(cs-uri-stem) AS GPage
FROM ex040528.log
GROUP BY date, cs-uri-stem WITH ROLLUP

A sample output would be:

date       cs-uri-stem         COUNT(ALL *) GDate GPage
---------- ------------------- ------------ ----- -----
2003-11-18 /Default.htm        1            0     0
2003-11-18 /style.css          1            0     0
2003-11-18 /images/address.gif 1            0     0
2003-11-18 /cgi-bin/counts.exe 1            0     0
2003-11-18 /data/rulesinfo.nsf 2            0     0
2003-11-19 /data/rulesinfo.nsf 6            0     0
2003-11-20 /data/rulesinfo.nsf 5            0     0
2003-11-20 /maindefault.htm    1            0     0
2003-11-20 /top2.htm           1            0     0
2003-11-20 /homelog.swf        1            0     0
-          -                   20           1     1
2003-11-18 -                   6            0     1
2003-11-19 -                   6            0     1
2003-11-20 -                   8            0     1

The values of the "GDate" field are 1 only for the rows in which the "date" field is NULL due to the introduction of the ROLLUP summary rows.
Similarly, the values of the "GPage" field are 1 only for the rows in which the "cs-uri-stem" field is NULL due to the introduction of the ROLLUP summary rows.

See also:

COUNT
SUM
AVG
MAX
MIN
PROPCOUNT
PROPSUM

GROUP BY
Aggregate Functions

Aggregating Data Within Groups

---

© 2004 Microsoft Corporation. All rights reserved.