GROUPING ID function in SQL Server

grouping_id function in sql server 2008
grouping_id in sql server 2008 example
sql server grouping level

In this video we will discuss
1. GROUPING_ID function in SQL Server
2. Difference between GROUPING and GROUPING_ID functions
3. Use of GROUPING_ID function

GROUPING_ID function computes the level of grouping.

Difference between GROUPING and GROUPING_ID

Syntax : GROUPING function is used on single column, where as the column list for GROUPING_ID function must match with GROUP BY column list.

GROUPING(Col1)

GROUPING_ID(Col1, Col2, Col3,…)

GROUPING indicates whether the column in a GROUP BY list is aggregated or not. Grouping returns 1 for aggregated or 0 for not aggregated in the result set.

GROUPING_ID() function concatenates all the GOUPING() functions, perform the binary to decimal conversion, and returns the equivalent integer. In short
GROUPING_ID (A, B, C) = GROUPING(A) + GROUPING(B) + GROUPING(C)

Let us understand this with an example.

SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales,
CAST(GROUPING(Continent) AS NVARCHAR(1)) +
CAST(GROUPING(Country) AS NVARCHAR(1)) +
CAST(GROUPING(City) AS NVARCHAR(1)) AS Groupings,
GROUPING_ID(Continent, Country, City) AS GPID
FROM Sales
GROUP BY ROLLUP(Continent, Country, City)

Row Number 1 : Since the data is not aggregated by any column GROUPING(Continent), GROUPING(Country) and GROUPING(City) return 0 and as result we get a binar string with all ZEROS (000). When this converted to decimal we get 0 which is displayed in GPID column.

Row Number 7 : The data is aggregated for Country and City columns, so GROUPING(Country) and GROUPING(City) return 1 where as GROUPING(Continent) return 0. As result we get a binar string (011). When this converted to decimal we get 10 which is displayed in GPID column.

Row Number 15 : This is the Grand total row. Notice in this row the data is aggregated by all the 3 columns. Hence all the 3 GROUPING functions return 1. So we get a binary string with all ONES (111). When this converted to decimal we get 7 which is displayed in GPID column.

Use of GROUPING_ID function : GROUPING_ID function is very handy if you want to sort and filter by level of grouping.

Sorting by level of grouping :

SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales,
GROUPING_ID(Continent, Country, City) AS GPID
FROM Sales
GROUP BY ROLLUP(Continent, Country, City)
ORDER BY GPID

Filter by level of grouping : The following query retrieves only continent level aggregated data
SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales,
GROUPING_ID(Continent, Country, City) AS GPID
FROM Sales
GROUP BY ROLLUP(Continent, Country, City)
HAVING GROUPING_ID(Continent, Country, City) = 3

Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/09/groupingid-function-in-sql-server.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/09/groupingid-function-in-sql-server_27.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists