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

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1

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