Grouping function in SQL Server

Pubblicato il: 25 settembre 2015
sul canale di: kudvenkat

81,119

445

sql server rollup grouping
sql server rollup replace null
sql server rollup grouping replace null

In this video we will discuss the use of Grouping function in SQL Server.

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.
/ @aarvikitchen5572

This is continuation to Part 104. Please watch Part 104 from SQL Server tutorial before proceeding.

What is Grouping function
Grouping(Column) 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.

The following query returns 1 for aggregated or 0 for not aggregated in the result set
SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales,
GROUPING(Continent) AS GP_Continent,
GROUPING(Country) AS GP_Country,
GROUPING(City) AS GP_City
FROM Sales
GROUP BY ROLLUP(Continent, Country, City)

What is the use of Grouping function in real world
When a column is aggregated in the result set, the column will have a NULL value. If you want to replace NULL with All then this GROUPING function is very handy.

SELECT CASE WHEN
GROUPING(Continent) = 1 THEN 'All' ELSE ISNULL(Continent, 'Unknown')
END AS Continent,
CASE WHEN
GROUPING(Country) = 1 THEN 'All' ELSE ISNULL(Country, 'Unknown')
END AS Country,
CASE
WHEN GROUPING(City) = 1 THEN 'All' ELSE ISNULL(City, 'Unknown')
END AS City,
SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY ROLLUP(Continent, Country, City)

Can't I use ISNULL function instead as shown below

SELECT ISNULL(Continent, 'All') AS Continent,
ISNULL(Country, 'All') AS Country,
ISNULL(City, 'All') AS City,
SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY ROLLUP(Continent, Country, City)

Well, you can, but only if your data does not contain NULL values. Let me explain what I mean.

At the moment the raw data in our Sales has no NULL values. Let's introduce a NULL value in the City column of the row where Id = 1
Update Sales Set City = NULL where Id = 1

Now execute the following query with ISNULL function

SELECT ISNULL(Continent, 'All') AS Continent,
ISNULL(Country, 'All') AS Country,
ISNULL(City, 'All') AS City,
SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY ROLLUP(Continent, Country, City)

Notice that the actuall NULL value in the raw data is also replaced with the word 'All', which is incorrect. Hence the need for Grouping function.

Please note : Grouping function can be used with Rollup, Cube and Grouping Sets

Text version of the video
http://csharp-video-tutorials.blogspo...

Slides
http://csharp-video-tutorials.blogspo...

All SQL Server Text Articles
http://csharp-video-tutorials.blogspo...

All SQL Server Slides
http://csharp-video-tutorials.blogspo...

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...

All Dot Net and SQL Server Tutorials in Arabic
/ kudvenkatarabic

In questa pagina del sito puoi guardare il video online Grouping function in SQL Server della durata di ore minuti seconda in buona qualità , che l'utente ha caricato kudvenkat 25 settembre 2015, condividi il link con amici e conoscenti, su youtube questo video è già stato visto 81,119 volte e gli è piaciuto 445 spettatori. Buona visione!

54,509