Getting started with SQL Server GROUPING SETS

Getting started with SQL Server GROUPING SETS

By definition, a grouping set is a group of columns by which you group. Typically, a single query with an aggregate defines a single grouping set.
For example, the following query defines a grouping set that includes brand and category which is denoted as (brand, category). The query returns the sales amount grouped by brand and category:
The following query returns the sales amount by brand. It defines a grouping set (brand):
SQL Server GROUPING SETS by brand
The following query returns the sales amount by category. It defines a grouping set (category):
SQL Server GROUPING SETS by brand
The following query defines an empty grouping set (). It returns the sales amount for all brands and categories.
The four queries above return four result sets with four grouping sets:
To get a unified result set with the aggregated data for all grouping sets, you can use the UNION ALL operator.
Because UNION ALL operator requires all result set to have the same number of columns, you need to add NULL to the select list to the queries like this:
The query generated a single result with the aggregates for all grouping sets as we expected.
However, it has two major problems:
  1. The query is quite lengthy.
  2. The query is slow because SQL Server needs to execute four subqueries and combines the result sets into a single one.
To fix these problems, SQL Server provides a subclause of the GROUP BY clause called GROUPING SETS.
The GROUPING SETS defines multiple grouping sets in the same query. The following shows the general syntax of the GROUPING SETS:
This query creates four grouping sets:
You can use this GROUPING SETS to rewrite the query that gets the sales data as follows:
As you can see, the query produces the same result as the one that uses the UNION ALL operator. However, this query is much more readable and of course more efficient.

GROUPING function

The GROUPING function indicates whether a specified column in a GROUP BY clause is aggregated or not. It returns 1 for aggregated or 0 for not aggregated in the result set.
See the following query example:
The value in the grouping_brand column indicates that the row is aggregated or not, 1 means that the sales amount is aggregated by brand, 0 means that the sales amount is not aggregated by brand. The same concept is applied to the grouping_category column.
In this tutorial, you have learned how to generate multiple grouping sets in a query by using the SQL Server GROUPING SETS.

Post a Comment