Group By in SQL Server with CUBE, ROLLUP and GROUPING SETS Examples

By: Bhavesh Patel | Comments (1) | Related: 1 | 2 | 3 | 4 | 5 | > TSQL

Problem

The GROUP BY clause in SQL Server allows grouping of rows of a query. Generally, GROUP BY is used with an aggregate SQL Server function, such as SUM, AVG, etc. In addition, the GROUP BY can also be used with optional components such as Cube, Rollup and Grouping Sets. In this tip, I will demonstrate various ways of building a GROUP BY along with output explained.

Solution

When building queries for reports, we often use the GROUP BY clause. There are also cases where having subtotals and totals as part of the output is helpful and this is where we will use the optional operators like: CUBE, ROLLUP and GROUPING SETS. These options are similar, but produce different results.

Create Sample SQL Server Database and Data

First, we will create a sample database, table and insert some data for our examples.

USE MASTER 
GO
 
CREATE DATABASE EmpTest 
GO 

USE EmpTest 
GO 

CREATE TABLE EmpSalary 
( 
  id INT PRIMARY KEY IDENTITY(1,1), 
  EmpName varchar (200), 
  Department varchar(100), 
  Category char(1), 
  Salary money 
) 
  
INSERT EmpSalary 
SELECT 'Bhavesh Patel','IT','A',$8000 
UNION ALL 
SELECT 'Alpesh Patel','Sales','A',$7000 
UNION ALL 
SELECT 'Kalpesh Thakor','IT','B',$5000 
UNION ALL 
SELECT 'Jay Shah','Sales','B',$4000 
UNION ALL 
SELECT 'Ram Nayak','IT','C',$3000 
UNION ALL 
SELECT 'Jay Shaw','Sales','C',$2000

Here is the data we just created.

SELECT * FROM EmpSalary

SQL Server GROUP BY Example

Below is a simple Group By query we SUM the salary data. In the first query, we group by Department and the second query we group by Department and Category.

SELECT 
   Department, 
   SUM(Salary) as Salary 
FROM EmpSalary 
GROUP BY Department
  
SELECT 
   Department, 
   Category, 
   SUM(Salary) as Salary 
FROM EmpSalary 
GROUP BY Department, Category

Below are the results. The first query returns 2 rows by Department with Salary totals, since there are 2 departments. The second query returns 6 rows by Department and Category with Salary totals, since there are 2 departments with 3 categories in each department.

SQL Server GROUP BY with HAVING Example

In the next example, we use the same group by, but we limit the data using HAVING which filters the data. In the examples below, for the first query we only want to see Departments where the total equals 16000 and for the second where the Department and Category total equals 8000.

SELECT 
   Department, 
   SUM(Salary) as Salary 
FROM EmpSalary 
GROUP BY Department
HAVING SUM(salary) = 16000
  
SELECT 
   Department, 
   Category, 
   SUM(Salary) as Salary 
FROM EmpSalary 
GROUP BY Department, Category
HAVING SUM(salary) = 8000

Below are the only 2 rows that meet the criteria. We can double check this by looking at the query results from the first set of queries above.

SQL Server GROUP BY CUBE Example

This example allows us to show all combinations of the data. This includes totals for the group combinations.

SELECT 
   Department, 
   SUM(Salary) as Salary 
FROM EmpSalary 
GROUP BY CUBE(Department) 
  
SELECT 
   Department, 
   Category, 
   SUM(Salary) as Salary 
FROM EmpSalary 
GROUP BY CUBE(Department, Category)

The first query results show the 2 Departments and the total, but also the grand total for these 2 Departments. The second query results show us all of the combinations of Department and Category. For example, we see IT (department) and A (category) and 16000 (total), then Sales (department) and A (category) and 7000 (total) and then NULL (both departments) and A (category) and 15000 (total). In the chart, I break down the different groupings that are part of this second query.

SQL Server GROUP BY ROLLUP Example

This is similar to the Group By Cube, but you will see the output is slightly different where we don't get as many rows returned for the second query.

SELECT 
   Department, 
   SUM(Salary) as Salary 
FROM EmpSalary 
GROUP BY ROLLUP(Department) 
  
SELECT 
   Department, 
   Category, 
   SUM(Salary) as Salary 
FROM EmpSalary 
GROUP BY ROLLUP(Department, Category)

We can see the first query results are the same as Group By Rollup example, but the second query only returns 9 rows instead of 12 that we got in the Group By Rollup query. The second query does the rollup first by Department and then by Category, which is different from the Group By Cube which did the rollup in both directions. This allows us to get subtotals for each Department and an overall total for all Departments.

We could change the second query, as shown below, to first rollup by Category and then Department.

SELECT 
   Department, 
   SUM(Salary) as salary 
FROM EmpSalary 
GROUP BY ROLLUP(Department) 
  
SELECT 
   Department, 
   Category, 
   SUM(Salary) as salary 
FROM EmpSalary 
GROUP BY ROLLUP (Category, Department)

We can see the results for the second query now do the grouping based on Category and then Department. And we still get the subtotals and totals.

SQL Server GROUP BY ROLLUP with GROUPING_ID Example

Another option is to use GROUPING_ID as part of the result set to show each group.

SELECT 
   Department, 
   Category, 
   SUM(Salary) as Salary,
   GROUPING_ID(Category, Department) as GroupingID 
FROM EmpSalary 
GROUP BY ROLLUP(Category, Department)

We can see we have the same results as above, but now we have a grouping value for each of these groups.

SQL Server GROUP BY GROUPING SETS Example

With grouping sets, we can determine how we want the data to be put together.

SELECT 
   Department, 
   Category, 
   SUM(Salary) as Salary 
FROM EmpSalary 
GROUP BY GROUPING SETS(Category, Department,(Category, Department),())

Below we can see we did a group for Category, another group for Department, another group for Category and Department and the last group for NULL.

Here is another example by Department and Category and an overall group for NULL.

SELECT 
   Department, 
   Category, 
   SUM(Salary) as Salary 
FROM EmpSalary 
GROUP BY GROUPING SETS((Department, Category),())

We could also take this a step further and use CUBE and ROLLUP for the different grouping sets.

SELECT 
   Department, 
   Category, 
   SUM(Salary) as Salary 
FROM EmpSalary 
GROUP BY GROUPING SETS(CUBE(Department, Category), ROLLUP(Department, Category))

Here is the output.

Summary

As per the reporting purpose for preparing a summarized output, we can use optional operators such as CUBE, ROLLUP, and GROUPING SETS in the query. GROUPING SETS is a controllable and scalable option, so I prefer to using it in lieu of ROLLUP and CUBE.

Next Steps

Group by in SQL Server
Aggregate functions in SQL Server
What is cube and rollup in SQL Server
Having in SQL Server

About the author

Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Thursday, June 25, 2020 - 7:02:03 AM - Salam	Back To Top (86042)
Bhavesh, nice and interesting article, in the 1st part of Grouping Sets you indicate "Below we can see we did a group for Category, another group for Department, another group for Category and Department and the last group for NULL." it is not clear for me where are the 4 groups !! Can you please add an image pointing to the 4 groups as you did in earlier ones.