The GROUPING TSQL Function

You can use the GROUPING function to indicate if a column in a resultset has been aggregated or not. A value of 1 will be returned if the result is aggregated, otherwise 0 is returned. It is best used to identify the additional rows returned when a query uses the ROLLUP, CUBE or GROUPING_SETS clauses.

To see this in action run the below TSQL in SSMS.

WITH MyCTE
(
	Division,
	[Quarter],
	Value
)
AS
(
	SELECT 'Sales 1', 1, 0.12
	UNION ALL
	SELECT 'Sales 1', 1, 0.34
	UNION ALL
	SELECT 'Sales 1', 2, 0.45
	UNION ALL
	SELECT 'Sales 2', 3, 0.77
	UNION ALL
	SELECT 'Sales 2', 1, 0.55
	UNION ALL
	SELECT 'Sales 1', 3, 0.78
	UNION ALL
	SELECT 'Sales 2', 2, 0.45
	UNION ALL
	SELECT 'Sales 2', 4, 0.67
	UNION ALL
	SELECT 'Sales 1', 4, 0.77
)
SELECT Division,
	   [Quarter],
	   SUM(Value) AS SummedValue,
	   GROUPING(Division) AS IsDivisionGroup,
	   GROUPING([Quarter]) AS IsQuarterGroup
FROM MyCTE
GROUP BY Division,
		 [Quarter]
WITH ROLLUP;

This query will return the following resultset. Note how IsQuarterGroup is flagged for each division total as well as the grand total row where IsQuarterGroup and IsDivisionGroup is also flagged.

grouping tsql function


Leave a Reply