Grouping and Subtotals the rows in SQL Server

During data analysis, data analysts must categorize data into categories so that different aspects of the information may be displayed in a more understandable manner. In order to build a data outline, it must also summarize distinct categories and Subtotals. This can be easily done in Excel, however it can be done in SQL Server as well using GROUPING function.

Let’s consider the popular “emp” and “dept” datasets. Please click here to get the code to create these tables in SQL Server.

EMP and DEPT datasets:

Now this data can be grouped in Excel along with Subtotals as shown below:

ExcelGroupingScr

Let’s do the same exercise using SQL Server.

Grouping: Is a function that computes the level of grouping. GROUPING_ID can be used only in the SELECT <select> list, HAVING, or ORDER BY clauses when GROUP BY is specified. Returning results aggregated by the columns in the GROUP BY clause is done with the ROLLUP and CUBE operators.

Let’s take a look at the output:

SELECT
DName AS Department,
Job AS Job,
SUM(Sal) as Salary_Sum,
GROUPING(DName) as GP_Department,
GROUPING(Job) as GP_Job
FROM Emp
JOIN Dept ON Dept.DeptNo = Emp.DeptNo
GROUP BY ROLLUP (Dname, Job)

GroupingWithNullsInSQL
The GROUPING function determines whether or not a column in the GROUP BY list has been aggregated. If the result set has been aggregated, it returns 1; otherwise, it returns 0.

The NULLs in the output shows the actual “subtotals” and “groups” hence let’s name them properly using ISNULL function. The changed code will be –

SELECT
ISNULL(DName,’All’) AS Department,
ISNULL(ISNULL(CONVERT(VARCHAR(30),Job),’All ‘+DName+’ Jobs’),’All Jobs’) AS Job,
SUM(Sal) as Salary_Sum,
GROUPING(DName) as GP_Department,
GROUPING(Job) as GP_Job
FROM Emp
JOIN Dept ON Dept.DeptNo = Emp.DeptNo
GROUP BY ROLLUP (Dname, Job)

SQLGrouping

The code-
ISNULL(ISNULL(CONVERT(VARCHAR(30),Job),’All ‘+DName+’ Jobs’),’All Jobs’) AS Job,
is to replace NULL with the Department name of its job positions. It also replaces the NULL for the “Grand total” row, which provides a summary of all the jobs’ salaries.

Hope you find this article helpful.

Please subscribe for more interesting updates.