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

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.

Advertisement

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
Image for post
Image for post

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.

Image for post
Image for post

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.

Advertisement

Image for post
Image for post

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.

Image for post
Image for post

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.

Image for post
Image for post

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.

Advertisement

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.

Image for post
Image for post

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.

Image for post
Image for post

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),())
Image for post
Image for post

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.

Image for post
Image for post

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.

Orijinal Tips : mssqltips.com

Written by

Yeni ol, önde ol. Eseriniz sizi yansıtsın!

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store