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


By:   |   Updated: 2020-05-27   |   Comments (1)   |   Related: More > T-SQL


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 GROUING 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
Basic Table Return from Query

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.

Performed Group By

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.

Performed Group By with Having

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.

Performed Group By with cube operator

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.

Performed Group By with ROLLUP

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.

Perform Group By with ROLLUP

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.

Use Grouping_ID for filter group result

 

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.

Group By Grouping SETS use case

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),()) 
Group By Grouping SETS use case

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.

Group By Grouping SETS use case

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


Last Updated: 2020-05-27


get scripts

next tip button



About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

View all my tips





Comments For This Article




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.



download





Recommended Reading

Cursor in SQL Server

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

SQL Server Loop through Table Rows without Cursor

Split Delimited String into Columns in SQL Server with PARSENAME








get free sql tips
agree to terms