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) GOThe 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 GOBelow 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 GOAnother 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.!
You didn't illustrate clearly benefits of GROUPING vs. CUBE/ROLLUP...
ReplyDeleteLoved this article
ReplyDeleteThis can be used for storing data ( META-DATA ), for data driven application. ( object, attrib, value,...)
ReplyDeletein existing SQL databases,
as it is done in noSQL paradigm ( manner )!
Please, give me some advice, I want to get :
ReplyDeleteCategoryName | SubCategorySplit
Main 1 Sub-1, Sub-2, Sub3
Main 2 Sub-1, Sub-2, Sub3
Thanks Paresh for this article.
ReplyDelete@Ronrap : what you are trying to do is not grouping as per SQL meaning, it's pure presentation :
SELECT DISTINCT MainCategoryName
, (
SELECT SubCategoryId + ', '
FROM dbo.GroupingTable AS S
WHERE M.MainCategoryName = S.MainCategoryName
FOR XML PATH('')
) AS SubCategoryId_List
FROM dbo.GroupingTable AS M
SELECT DISTINCT MainCategoryName
, S.SubCategoryId_List
FROM dbo.GroupingTable AS M
CROSS APPLY (
SELECT SubCategoryId + ', '
FROM dbo.GroupingTable AS S
WHERE M.MainCategoryName = S.MainCategoryName
FOR XML PATH('')
) AS S (SubCategoryId_List)