Indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. GROUPING can be used only in the SELECT <select> list, HAVING, and ORDER BY clauses when GROUP BY is specified.
GROUPING ( <column_expression> )
Is a column or an expression that contains a column in a GROUP BY clause.
GROUPING is used to distinguish the null values that are returned by ROLLUP, CUBE or GROUPING SETS from standard null values. The NULL returned as the result of a ROLLUP, CUBE or GROUPING SETS 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 in the AdventureWorks2012 database. The
GROUPING function is applied to the
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
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)