GROUPING SETS Operator in SQL SERVER(TSQL)

SQL Server supports grouping of data with GROUP BY since older versions, and also offers WITH ROLLUP and WITH CUBE operators for grand-total and sub-totals. SQL Server 2008 provides a new operator named GROUPING SETS. With SQL Server 2008, it is recommended to use GROUPING SETS instead of WITH ROLLUP or WITH CUBE operators.

You can find many detailed examples about GROUPING SETS, ROLLUP and CUBE operators in this article.

And alson you can find the details of GROUP BY, WITH ROLLUP and WITH CUBE in the below articles.

“Group By Clause, SUM, MAX, MIN, AVG, COUNT and COUNT_BIG Functions in SQL Server“,

“WITH ROLLUP and WITH CUBE Operators in SQL Server(TSQL)”

Also you should read the below article too.

“GROUPING and GROUPING_ID Functions in SQL Server(TSQL)”

Let’s make examples for a better understanding of GROUPING SETS Operator.

Example:

First, we create a table with the help of the following script and add a few records into this table.

This table has 3 fields that we can use as a dimension. (Location, Month and Category).

We will also use the Price column as KPI / Numeric Indicator. Our goal is to see the sum of each dimension at the same time as below.

As can be seen from the result of below query, the sum of the Month and Location data appears in the same list. To do this in SQL, it is necessary to group each field and merge it with UNION.

In this way, when we want to include more than one dimension, we must create a UNION series for each. The GROUPING SETS clause that comes with SQL Server 2008 offers both flexibility and performance. This sentence, which is compatible with ANSI SQL 2006, can be considered as a function used with GROUP BY. As a parameter, it is enough to write the columns to be aggregated.

Or let’s replace NULL values to get exactly the same image in our example.

Using the three dimensions in our example, let’s calculate all possibilities separately without GROUPING SETS and using GROUPING SETS.

When you run the above two queries, a result returns as follows.

Let’s calculate the sub-total and grand-total by using the Location and Month dimensions.

To make it clearer, let’s sort:

Instead of combining this type of process with UNION for each dimension, we do this with WITH ROLLUP or WITH CUBE. So we’ll get the same result with an easier typing.

To get the same result as GROUPING SETS, we need to write the fields to be aggregated.

As you can see, there is flexibility in the GROUPING SETS method. WITH ROLLUP automatically creates a grand total for all fields grouped, while GROUPING SETS creates only for what we do. For example, if you do not want to calculate the grand total, simply remove the line “()” in GROUPING SETS.

GROUPING SETS without Grand Total:

GROUPING SETS without Sub Total and Grand Total:

GROUPING SETS with only Grand Total:

Let’s add the “Month” field to GROUPING SETS. Thus, all possible aggregated rows will be created for Location and Month fields. We were doing this with WITH CUBE.

WITH CUBE Method:

WITH GROUPING SETS Method:

The order of use of the dimensions in GROUPING SETS is not important. It always returns the same result.

Let’s add 3 fields in our example without grand-total.

One of the advantages offered by GROUPING SETS is that sub-totals can only be created for certain columns. In our previous example, a sub-total row was created for all groups. However, we can create a sub-total for the Month and Category by fixing the location.

Another Example:

CONCLUSION:

With SQL Server 2008, it is recommended to use GROUPING SETS instead of WITH ROLLUP or WITH CUBE operators.