How to use GROUPING SETS in ‘Group By’ aggregate function in SQL Server Instance

“GROUPING SETS” creates a group for each combination and adds results into subtotals and grand totals and displays only those calculations.

It might actually be more convenient mostly to execute two different queries instead of GROUPING SETS because then one can have the grouped results separate from the totals. But GROUPING SETS will more efficient because SQL Server will use an execution plan that calculates all of the aggregations together in one pass. Compare that to the UNION ALL which would provide the exact same results but use a less efficient execution plan (two table scans instead of one).

Imagine an extreme example in which you are working on a data set so large that each scan of the data takes one whole hour. You have to provide totals on basically every possible dimension (way to slice) that data every day. Then GROUPING SETS option is exactly what you need. If you save off the results of that one scan into a special schema layout, you will then be able to run reports for the rest of the day off the saved results.

So, if you are working on a data warehousing project then GROUPING SETS is for you. For the rest it mostly falls into the “neat thing to know” category.

Let’s see it in action,

We first create a test temp table with dummy data;

CREATE TABLE #Test
(
Country   VARCHAR (50),
State     VARCHAR (50),
Code     INT
)

INSERT INTO #Test VALUES(‘Hennepin’, ‘Minneapolis’,1 )
INSERT INTO #Test VALUES(‘Hennepin’, ‘Minneapolis’,2 )
INSERT INTO #Test VALUES(‘Hennepin’, ‘Minneapolis’,3)
INSERT INTO #Test VALUES(‘Hennepin’, ‘Minneapolis’,4)
INSERT INTO #Test VALUES(‘Hennepin’, ‘StPaul’,5)
INSERT INTO #Test VALUES(‘Hennepin’, ‘StPaul’,6)
INSERT INTO #Test VALUES(‘Hennepin’, ‘StPaul’,7)
INSERT INTO #Test VALUES(‘Hennepin’, ‘StPaul’,8)
INSERT INTO #Test VALUES(‘Hennepin’, ‘Minnetonka’,9)
INSERT INTO #Test VALUES(‘Hennepin’, ‘Minnetonka’,10)
INSERT INTO #Test VALUES(‘Hennepin’, ‘Minnetonka’,11 )
INSERT INTO #Test VALUES(‘Hennepin’, ‘Minnetonka’,12)

Before we start let’s take a look at the table we created;

select * from #Test;

GROUPING SETS in ‘Group By’ aggregate function in SQL

Simple example of the a group by clause with a Sum() aggregated function;

SELECT Country, State,
SUM (Code) AS Code
FROM #Test
GROUP BY Country, State;

GROUPING SETS in ‘Group By’ aggregate function in SQL

Now finally let’s use GROUPING SETS() function in addition to the group by clause;

SELECT Country, State,
SUM (Code) AS Code
FROM #Test
GROUP BY GROUPING SETS (Country, State);

GROUPING SETS in ‘Group By’ aggregate function in SQL

Here is the same data just added more groups. Simple example of how to have multiple GROUPING SETS in the same GROUP BY clause;

SELECT Country, State, SUM (Code) AS Code
FROM #Test
GROUP BY GROUPING SETS ((Country, State),
(State),
(Country),
( ) -- no grouping
);

GROUPING SETS in ‘Group By’ aggregate function in SQL

Interested in to have more control over your database for your Business?  Anyon Consulting- Database Management Consulting group can help.  Contact us today to learn more about our performance analytics and improvement services.

Scroll to top