# 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.

# 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 DepartmentHAVING SUM(salary) = 16000  SELECT    Department,    Category,    SUM(Salary) as Salary FROM EmpSalary GROUP BY Department, CategoryHAVING 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.

Orijinal Tips : mssqltips.com

Written by