Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Grouping function in SQL Server

Suggested Videos
Part 102 - Rollup in SQL Server
Part 103 - Cube in SQL Server
Part 104 - Difference between cube and rollup in SQL Server



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



This is continuation to Part 104. Please watch Part 104 from SQL Server tutorial before proceeding. We will use the following Sales table for this example.
sql server rollup grouping

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)

Result : 
Grouping function in SQL Server

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)

Result : 
sql server rollup replace null

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)

Result : 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.
sql server rollup grouping replace null

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

1 comment:

  1. Hi Sir,
    With due respect and humble submission, I have a small query to you. I have a requirement in an asp.net application that if the user is already logged in , and if he opens another browser window and tries to login again, the application should throw an error stating he is already logged in another browser window.
    Please upload the video in this regards as soon as possible.
    Thnx.

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.