The GROUP BY clause in SQL Server allows us to group rows of a query based on one or more columns. It is commonly used with aggregate functions like SUM, AVG, etc. However, there are also optional operators like CUBE, ROLLUP, and GROUPING SETS that can be used to generate more advanced and flexible grouping results.

Creating Sample Database and Data

Before we dive into the examples, let’s create a sample database, table, and insert some data for our demonstrations:

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 

Simple GROUP BY Example

Let’s start with a simple GROUP BY query where we calculate the total salary for each department:

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

The result of this query will be a list of departments along with their respective total salaries.

GROUP BY with HAVING Example

We can also use the GROUP BY clause in combination with the HAVING clause to filter the grouped data based on certain conditions. For example, let’s find departments with a total salary of 16000:

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

This query will return only the departments that have a total salary of 16000.

GROUP BY CUBE Example

The CUBE operator allows us to generate all possible combinations of the grouped data, including the grand total. Let’s see an example:

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

This query will return the total salary for each department, as well as the grand total for all departments.

GROUP BY ROLLUP Example

The ROLLUP operator is similar to CUBE, but it generates a slightly different output. Let’s take a look:

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

This query will return the total salary for each department, as well as subtotals for each department and an overall total for all departments.

GROUP BY ROLLUP with GROUPING_ID Example

We can use the GROUPING_ID function to include a grouping identifier in the result set. This can be useful when working with multiple levels of grouping. Here’s an example:

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

This query will return the total salary for each category and department, along with a grouping identifier for each group.

GROUP BY GROUPING SETS Example

The GROUPING SETS operator allows us to specify how we want the data to be grouped. Here’s an example:

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

This query will generate groups based on category, department, category and department together, and a final group for NULL values.

Conclusion

The GROUP BY clause in SQL Server is a powerful tool for grouping and summarizing data. By using optional operators like CUBE, ROLLUP, and GROUPING SETS, we can further customize the grouping results to meet our reporting needs. Understanding these concepts will help us generate more meaningful and insightful reports from our SQL Server databases.