SQL Server GROUPING SETS

GROUPING SET is an extension of the GROUP BY clause. The GROUP BY statement is GROUPING SET is an extension of the GROUP BY clause. The GROUP BY statement is used to summarize the data in conjunction with aggregate functions such as SUM, AVG, COUNT, etc. It groups the result set based on the single or multiple columns. The GROUPING SET was first introduced with the SQL Server 2008 version.

The following scenario explains the concept of GROPING SET in SQL Server:

SQL Server allows us to create a group by combining the department and city of an employee table to retrieve the total number of employees who are working in a particular department in a city. Suppose we want to get all employee who works in the IT department in Bangalore city. Also, we can further add employee type (writer) to department and city to retrieve all writers working in the IT department in Bangalore city. These are the two different groups so that it cannot be performed in a single query using the GROUP BY clause. We need to write two different statements for these groups where the first is the (department, city) and the second is the (department, city, employee type).

In such a case, we use the GROUPING SET that makes it simple. It enhances the GROUP BY clause's functionality by specifying many groups in a single query. It allows the creation of many groups and analyze and compare their properties using a GROUPING SET in a single query as it is a collection of multiple GROUPS.

Syntax

The following are the syntax that illustrates the GROUPING SET in SQL Server:

SELECT column_list, aggregate(column_name)
FROM table_name
GROUP BY
GROUPING SETS (
(column1, column2),
(column1),
(column2),
()
);

This syntax parameter is explained below:

Column_list: It indicates the column's name that we want to retrieve in a result set from the table.
aggregate(column_name): It indicates the column on which aggregate function (i.e. SUM, COUNT, AVG etc.) is applied
GROUP BY: It is used to specify a column or list of columns as a single group.
GROUPING SET: It indicates the set of columns that have to be grouped together.
(): It is optional that specifies an empty grouping set and aggregates columns individually.

GROUPING SETS Equivalents

GROUPING SETS is equivalent to a UNION ALL operator. SQL Server produces the same information using the UNION ALL query, each having the GROUP BY list and GROUPING SETS defined as the GROUP BY list. GROUPING SETS can also be used in place of ROLLUP and CUBE.

The below table explains GROUPING SETS equivalent UNION ALL, ROLLUP, and CUBE more clearly:

GROUPING SETS equivalent to UNION ALL
SELECT column1, column2, SUM(colmn3) FROM table1 GROUP BY GROUPING SETS ((column1), (column2));	SELECT column1 NULL AS column2, SUM(column3) FROM table1 GROUP BY column1 UNION ALL SELECT NULL AS column1, column2, SUM(column3) FROM table1 GROUP BY column2;
GROUPING SETS equivalent to ROLLUP
GROUP BY ROLLUP (column1, column2, column3)	GROUP BY GROUPING SETS ((column1, column2, column3), (column1, column2), (column1), ());
GROUPING SETS equivalent to CUBE
GROUP BY CUBE (column1, column2, column3)	GROUP BY GROUPING SETS ( (column1, column2, column3), (column1, column2), (column1, column3), (column2, column3), (column1), (column2), (column3), ());

GROUPING SETS Example

Let us look at how GROUPING SETS works in SQL Server with examples. We will demonstrate this by first creating a sample table named ProductQty that contains the details of the purchased product and then insert some records in the table.

Execute the below statement to create a ProductQty table:

CREATE TABLE ProductQty(
 Id INT IDENTITY(1,1),
 Region VARCHAR(100),
 Product VARCHAR(50),
 Year INT,
 Quantity INT
)

Next, execute the below statement to insert records into this table:

INSERT INTO ProductQty(Region, Product, Year, Quantity)
VALUES('East', 'Computer', 2020, 13000),
('South', 'Computer', 2020, 45000),
('North', 'Computer', 2020, 25000),
('East', 'Hard Disk', 2020, 1900),
('West', 'Computer', 2021, 25000),
('South', 'Hard Disk', 2021, 5500),
('West', 'Hard Disk', 2021, 6500),
('East', 'Pen Drive', 2021, 1200),
('North', 'Mouse', 2019, 1600),
('South', 'Pen Drive', 2019, 2700),
('East', 'Mouse', 2019, 2000),
('West', 'Pen Drive', 2019, 1900);

Now, we can verify the data by executing the SELECT statement:

Here we will start with the GROUP BY clause instead of starting with the GROUPING SET so that we can gradually extend the logic and clearly understand the significance and usage of the GROUPING SET.

1) Query to determine the total purchased quantity by region

This statement creates a GROUP using the 'Region' column to get the quantity of the total purchases. Here we use the aggregate function SUM on the 'Quantity' column for the summation of the total purchases.

SELECT Region, SUM(Quantity) AS TotalPurchase 
FROM ProductQty 
GROUP BY Region;

Executing the query will display the total purchase quantity:

2) Query to determine the total purchased quantity in each year

This statement creates a GROUP using the 'Year' column to get the quantity of the total purchases in each year. Here we also used the SUM function for the summation of the total purchases.

SELECT Year, SUM(Quantity) AS TotalPurchase 
FROM ProductQty 
GROUP BY Year;

Executing the query will display the total purchase quantity:

3) Query to determine the total purchase quantity available in each region and year

This statement creates a GROUP using the 'Region' and 'Year' columns and returns the total number of purchases in each region and year.

SELECT Region, Year, SUM(Quantity) AS TotalPurchase 
FROM ProductQty 
GROUP BY Region, Year;

Executing the query will display the total purchase quantity:

4) If we want to display the overall purchase quantity, we use the empty group set query:

SELECT SUM(Quantity) AS TotalPurchase 
FROM ProductQty;

Executing the query will display the total purchase quantity:

As we can see, the above four queries return four result set using different grouping sets. Here we have an issue to execute a fresh query each time to create a new group or category for analyzing the data from a different point of view. SQL Server has another way also to get the same result by combining all the query result sets into a single query using the UNION ALL operator[.

The following UNION ALL statement yields the same result. Because the UNION ALL operator demands that all participating queries' result sets have the same number of columns, we must add a dummy column NULL to the select list to ensure that all statements have the same number of columns.

SELECT Region, NULL AS Product, SUM(Quantity) AS TotalQty  
FROM ProductQty 
GROUP BY Region
UNION ALL
SELECT NULL AS Region, Product, SUM(Quantity) AS TotalQty
FROM ProductQty
GROUP BY Product
UNION ALL
SELECT Region, Product, SUM(Quantity) AS TotalQty  
FROM ProductQty
GROUP BY Region, Product
UNION ALL
SELECT NULL AS Region, NULL AS Product, SUM(Quantity) AS TotalQty 
FROM ProductQty

Executing the statement will display the below output where we see that the UNION ALL generate a single result with the aggregates for all grouping sets as expected. This result set is also an integrated output of all the above four queries.

However, this approach returns the result within a single query, but it is ad-hoc, cumbersome, and inefficient. The database server has a lot of pressure to execute this approach that can cause slowness of query and performance issues as it must conduct four different queries. Here database server first combines the result sets of the first two queries, then combine those result sets with the third query and then with the fourth query. This approach needs several table reads, temporary storage, and many IOs.

SQL Server 2008 introduced the GROUPING SET capability to overcome these shortcomings, allowing us to define many GROUPS within a single query. We can achieve the same goal by including all GROUPS in the GROUPING SET clause, resulting in an output that includes all groups and their essential details. Here is the query that defines each grouping set within a single statement:

SELECT Region, Year, SUM(Quantity) AS TotalPurchaseQty  
FROM ProductQty 
GROUP BY GROUPING SETS
((Region), 
(Year), 
(Region, Year), 
()) 
ORDER BY Region, Year;

Executing the statement will produce the following output:

In this output, we can see that this query gives the same result and is also very simple and efficient compared to using the UNION ALL query. Here we can see that it has one row (first row), which is not a group; it is just an aggregate of the total quantity.

GROUPING Function

SQL Server also enables us to check whether the specified column in the GROUP BY clause is aggregated or not by using the GROUPING FUNCTION in the above query. This function returns two values, 0 & 1, where 1 represents the aggregated result set, and 0 represents not aggregated.

The following query explains the use of the grouping function:

SELECT GROUPING(Region) AS Is_RegionGrouped, GROUPING(Year) AS Is_YearGrouped, 
Region, Year, SUM(Quantity) AS TotalPurchaseQty  
FROM ProductQty 
GROUP BY GROUPING SETS ((Region), (Year), (Region, Year), ( )) 
ORDER BY Region, Year;

Executing the statement will display the below output:

In this output, the column Is_RegionGrouped indicates 1 when the product quantity is aggregated by region, 0 means that the product quantity is not aggregated by region. The same concept is applied to the Is_YearGrouped column.

Conclusion

This article demonstrates in detail to generate many grouping sets in a query with the help of GROUPING SETS. Here we also learned the shortcoming of several GROUP BY clauses and UNION ALL operators within a single statement.

Next TopicSQL Server Primary Keys

← prev next →