Skip to content

Latest commit

 

History

History
76 lines (60 loc) · 3.13 KB

grouping-transact-sql.md

File metadata and controls

76 lines (60 loc) · 3.13 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
GROUPING (Transact-SQL)
GROUPING (Transact-SQL)
markingmyname
maghan
12/03/2019
sql
t-sql
reference
GROUPING
GROUPING_TSQL
null values [SQL Server], GROUPING function
grouping columns
ROLLUP operator
GROUP BY clause, GROUPING function
GROUPING function
CUBE operator
TSQL
= azuresqldb-current || = azuresqldb-mi-current || >= sql-server-2016 || >= sql-server-linux-2017 || = azuresqledge-current || = azure-sqldw-latest||=fabric

GROUPING (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw]

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.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

GROUPING ( <column_expression> )

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 [!INCLUDEssSampleDBnormal] 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.

[!INCLUDEssResult]

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)

See Also

GROUPING_ID (Transact-SQL)
GROUP BY (Transact-SQL)