Export (0) Print
Expand All
Expand Minimize

GROUPING (Transact-SQL)

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.

Topic link icon Transact-SQL Syntax Conventions


GROUPING ( column_name ) 

column_name

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

tinyint

Grouping is used to distinguish the null values that are 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. This acts as a column placeholder in the result set and means all.

The following example groups SalesQuota and aggregates SaleYTD amounts. The GROUPING function is applied to the SalesQuota column.

USE AdventureWorks;
GO
SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping'
FROM Sales.SalesPerson
GROUP BY SalesQuota WITH ROLLUP;
GO

The result set shows two null values under SalesQuota. 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 TotalSalesYTD amounts for all SalesQuota groups and is indicated by 1 in the Grouping column.

Here is the result set.

SalesQuota     TotalSalesYTD        Grouping 
---------      -------------         --------
NULL           1533087.5999          0
250000.00      33461260.59           0
300000.00      9299677.9445          0
NULL           44294026.1344         1

(4 row(s) affected)

Change History

Release History

12 December 2006

Changed content:
  • Removed OVER clause.
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft