Understanding Grouping Sets in SQL

To know the proper use of "Grouping Set," we should first understand the use of "Group By."

Normally, "Group By" is used to group similar kinds of data and display unique records. Aggregate functions can also be applied to calculate data. For example, we have different models of cars and trucks along with their quantities. Let’s take a look at the data in the table below:

Now we can add (sum) the number of vehicles based on their color, category, type, or all of these together. The SQL "Group By" command can group values by the selected columns.

For instance:

Why Use Grouping Sets?

What if we need to see totals for each individual key performance indicator (KPI) and the overall totals for all KPIs? In this case, we use "Grouping Sets" in SQL Server—a very powerful feature.

In this query, we calculate the total number of vehicles based on their category, type, and color, as well as combinations of these attributes.

Grouping Sets allow us to aggregate data on multiple levels, whereas "Group By" only works with all specified fields in the query.