HomeОбразованиеRelated VideosMore From: kudvenkat

GROUPING ID function in SQL Server

131 ratings | 29725 views
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
Html code for embedding videos on your blog
Text Comments (17)
pancha Subba (9 months ago)
It is commonly taught that any number to the zero power is 1, and zero to any power is 0. But if that is the case, what is zero to the zero power? Well, it is undefined (since xy as a function of 2 variables is not continuous at the origin) this will help to understand this session
Al S (1 year ago)
I am not understanding the concept of 2 power 1..etc. Where can ai get more information on this?
jonas vermassen (1 year ago)
Your way of explaining things is sublime, thank you.
Bruce Harr (1 year ago)
Wow, this was exactly what I was looking for. Thank you!
stephen barter (1 year ago)
Great video but what if I want to sort the data by say for example San Francisco, California BUT NOT Houston Texas... do I just filter using a WHERE CLAUSE or HAVING?
bussa kishore (1 year ago)
YOU ARE AMAZING STAR, KEEP ROCKING
Mohammed Hitham (1 year ago)
Greate video thanks alot
Shuwei Li (2 years ago)
Dude you are a life saver for real!!!
MCr33py (2 years ago)
Excellent explanation. Thanks!!
kudvenkat (2 years ago)
Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful. I have organised all the Dot Net & SQL Server videos in to playlists, which could be useful to you https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd If you need DVDs or to download all the videos for offline viewing please visit http://www.pragimtech.com/kudvenkat_dvd.aspx Slides and Text Version of the videos can be found on my blog http://csharp-video-tutorials.blogspot.com Tips to effectively use my youtube channel. https://www.youtube.com/watch?v=y780MwhY70s If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. http://www.youtube.com/subscription_center?add_user=kudvenkat If you like these videos, please click on the THUMBS UP button below the video. May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them. Good Luck Venkat
唐聖傑 (2 years ago)
Thank you very useful video ^^
Rudra Shivgan (3 years ago)
The Greatest Tech Teacher I have Ever Seen In My Life.Every Concepts is Properly Explained and Thoroughly Covered.Simply Great...Hands Down
Muhammad Rehbar Sheikh (3 years ago)
Thanks venkat sir!! Keep doing...!
RAQIBUL ALAM RASHED (3 years ago)
I have nothing to say.Awesome really awesome.Thanks and keep doing this great job.
Paulo Ts (3 years ago)
Kudvenkat, Could you make a video about dynamic SQL as soon as you can? Thanks and keep doing this great job.
Kostas Greek (3 years ago)
you are god..
Tan YEW MENG (3 years ago)
+Kostas Greek No doubt Venkat is God

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.