Group By With GROUPING SETS in SQL Server 2008

Problem:
Whenever we require aggregate data we use Group by. Using Group by we can get the aggregate data, but if it is required to get the data using a different set of columns, then we need to write the separate queries using different sets with union all with all queries.

Solution:
SQL Server 2008 came up with new enhancement of group by, This enhancement is GROUPING SETS. With GROUPING SETS we get the aggregate data with different sets of data in a t-SQL statement. Let's discuss this group by enhancement with better example. Creating database and table objects.

CREATE DATABASE GroupingDB

GO

USE GroupingDB

GO

IF (OBJECT_ID('GroupingTable') > 0)
DROP TABLE GroupingTable

CREATE TABLE GroupingTable
  (
     MainCategoryName VARCHAR(100)
     ,SubCategoryId   VARCHAR(100)
     ,VALUE           BIGINT
  )

GO 

-- Inserting demo records in table.
INSERT INTO GroupingTable
VALUES
('Main-1','Sub-1',100),
('Main-1','Sub-2',200),
('Main-2','Sub-1',100),
('Main-1','Sub-3',300),
('Main-1','Sub-3',400),
('Main-2','Sub-1',100),
('Main-2','Sub-2',500),
('Main-3','Sub-1',600),
('Main-3','Sub-3',200)

GO

The logical image has the values by various sets as follows.

Now we are using native methods to get the total sum of values of records by Main category and sub category wise.

SELECT
  SUM(VALUE) as Total
FROM   GroupingTable

GO 

SELECT
  MainCategoryName
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY MainCategoryName 

GO

SELECT
  SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY SubCategoryId 

GO

SELECT
  MainCategoryName
  ,SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY
  MainCategoryName
  ,SubCategoryId

GO 

Implementation:
Now we are using new methods or enhancement Grouping Sets and see the result set.

SELECT
  MainCategoryName
  ,SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY
GROUPING SETS
(
  (MainCategoryName ,SubCategoryId),
  (MainCategoryName),
  (SubCategoryId),
  ()

)
ORDER BY MainCategoryName,SubCategoryId

GO

It has also introduced CUBE and ROLLUP. Let's check with them how they are same with resultset of Grouping Sets. Below script will give the same result sets CUBE or Grouping sets.

SELECT
  MainCategoryName
  ,SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY
CUBE (MainCategoryName,SubCategoryId)

--or

SELECT
  MainCategoryName
  ,SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY
GROUPING SETS
(
  (MainCategoryName ,SubCategoryId),
  (MainCategoryName),
  (SubCategoryId),
  ()

)
ORDER BY MainCategoryName,SubCategoryId

GO

Below script will give the same result sets ROLLUP or Grouping sets.

SELECT
  MainCategoryName
  ,SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY
ROLLUP (MainCategoryName,SubCategoryId)

--or

SELECT
  MainCategoryName
  ,SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY
GROUPING SETS
(
  (MainCategoryName ,SubCategoryId),
  (MainCategoryName),
  ()

)
ORDER BY MainCategoryName,SubCategoryId

GO

Another function is GROUPING_ID () which return or compute the level of grouping.

SELECT
  MainCategoryName
  ,SubCategoryId,
  GROUPING_ID(MainCategoryName,SubCategoryId),
  GROUPING(MainCategoryName),
  GROUPING(SubCategoryId)
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY
ROLLUP (MainCategoryName,SubCategoryId)
GO

Now cover your code with new enhancement.!