GROUPING (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
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.
Transact-SQL syntax conventions
Syntax
GROUPING ( <column_expression> )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
<column_expression>
Is a column or an expression that contains a column in a GROUP BY clause.
Return Types
tinyint
Remarks
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.
Examples
The following example groups SalesQuota
and aggregates SaleYTD
amounts in the AdventureWorks2022 database. The GROUPING
function is applied to the SalesQuota
column.
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)