Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize
8 out of 14 rated this helpful - Rate this topic

GROUPING

SQL Server 2000

Is an aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP.

Grouping is allowed only in the select list associated with a GROUP BY clause that contains either the CUBE or ROLLUP operator.

Syntax

GROUPING ( column_name )

Arguments

column_name

Is a column in a GROUP BY clause to check for CUBE or ROLLUP null values.

Return Types

int

Remarks

Grouping is used to distinguish the null values returned by CUBE and ROLLUP from standard null values. The NULL returned as the result of a CUBE or ROLLUP operation is a special use of NULL. It acts as a column placeholder in the result set and means "all."

Examples

This example groups royalty and aggregate advance amounts. The GROUPING function is applied to the royalty column.

USE pubs
SELECT royalty, SUM(advance) 'total advance', 
   GROUPING(royalty) 'grp'
   FROM titles
   GROUP BY royalty WITH ROLLUP

The result set shows two null values under royalty. The first NULL represents the group of null values from this column in the table. The second NULL is in the summary row added by the ROLLUP operation. The summary row shows the total advance amounts for all royalty groups and is indicated by 1 in the grp column.

Here is the result set:

royalty        total advance              grp 
---------      ---------------------    ---
NULL           NULL                     0  
10             57000.0000               0  
12             2275.0000                0  
14             4000.0000                0  
16             7000.0000                0  
24             25125.0000               0  
NULL           95400.0000               1  

See Also

Aggregate Functions

SELECT

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.