Multiple Grouping Sets in SQL Server

We know the GROUP BY clause groups table data. With it we can also do multiple grouping sets. We need a single query to group the data in multiple combinations.

In SQL Server three clauses allow multiple grouping sets, grouping sets, Cube and Rollup.

Each of them is shown in this article with examples.

USE DEMOS ;

Create the EMP Table

CREATE TABLE DBO.EMP
(
ID INT IDENTITY(1, 1) PRIMARY KEY,
FIRTSNAME VARCHAR(100),
LASTNAME VARCHAR(100),
LOCATION VARCHAR(100),
DOB DATETIME,
SALARY MONEY,
DEPT INT
)

Merge Statement

Insert data using Merge statement as in the following:

MERGE INTO DBO.EMP AS T_EMP USING (
VALUES
(
'RAKESH', 'KALLURI', 'HYDERABAD',
'07-23-1989', 24000, 1
),
(
'NARESH', 'CH', 'PUNE', '07-23-1987',
48000, 1
),
(
'SRUJAN', 'KUMAR', 'HYDERABAD', '07-23-1988',
25000, 1
),
(
'VENKATESH', 'BODUPPALY', 'HYDERABAD',
'07-23-1986', 32000, 2
),
(
'ALI', 'MD', 'HYDERABAD', '07-23-1987',
38000, 2
),
(
'GANGA', 'RAJAYAM', 'PUNE', '05-26-1987',
390000, 2
),
(
'RAVI', 'KUMAR', 'CHENNAI', '03-23-1986',
47000, 1
),
(
'PRAVEEN', 'KUMAR', 'DELHI', '07-23-1988',
33000, 2
)
) AS S_EMP(
FIRTSNAME, LASTNAME, LOCATION, DOB,
SALARY, DEPT
) ON 1 = 2 WHEN NOT MATCHED THEN INSERT(
FIRTSNAME, LASTNAME, LOCATION, DOB,
SALARY, DEPT
)
VALUES
(
S_EMP.FIRTSNAME, S_EMP.LASTNAME,
S_EMP.LOCATION, S_EMP.DOB, S_EMP.SALARY,
S_EMP.DEPT
);

Check the Data

SELECT * FROM DBO.EMP;

SELECT DEPT ,COUNT(*) [COUNT] FROM DBO.EMP GROUP BY DEPT;
SELECT YEAR(DOB) ,COUNT(*) [COUNT] FROM DBO.EMP GROUP BY YEAR(DOB);

Grouping Sets

In grouping sets we can provide multiple grouping sets with the () parenthesis symbol. The new grouping is separated by a "," (comma). If we want empty grouping set means all rows combination ().

(): Means an empty grouping set.

Example

SELECT DEPT,YEAR(DOB) [YEAR],COUNT(*) [COUNT] FROM DBO.EMP
GROUP BY
GROUPING
SETS ((DEPT, YEAR(DOB)), (DEPT), (YEAR(DOB)), () );

In the preceding example 4, the grouping sets combinations of data is grouped together. By using Union or Union all, we can get the same result.

SELECT
DEPT,
YEAR(DOB) [COUNT],
COUNT(*) [COUNT]
FROM
DBO.EMP
GROUP BY
DEPT,
YEAR(DOB)
UNION ALL
SELECT
DEPT,
NULL [COUNT],
COUNT(*) [COUNT]
FROM
DBO.EMP
GROUP BY
DEPT
UNION ALL
SELECT
NULL DEPT,
YEAR(DOB) [COUNT],
COUNT(*) [COUNT]
FROM
DBO.EMP
GROUP BY
YEAR(DOB)
UNION ALL
SELECT
NULL DEPT,
NULL [COUNT],
COUNT(*) [COUNT]
FROM
DBO.EMP

The four possible combinations are:



Cube

The Cube clause also defines the list of grouping sets, it also generates the empty grouping set.

Example

SELECT DEPT,YEAR(DOB) [YEAR],COUNT(*) [COUNT] FROM DBO.EMP
GROUP BY CUBE((DEPT),(YEAR(DOB)))

Rollup

The Rollup clause gives the hierarchy form result, it also generates the empty grouping set.

Example

SELECT
DEPT,
YEAR(DOB) [YEAR],
COUNT(*) [COUNT]
FROM
DBO.EMP
GROUP BY
ROLLUP(
(DEPT),
(
YEAR(DOB)
)
)

Here hierarchy means in the preceding example 3 possible combinations exist

1. (Dept, Year(DOB))
2. (Dept)
3. ()
   
   
   

Grouping

The Grouping function identifies the grouping set that accepts one input column, if that column is in that grouping set then the result is 0. Grouping can be used in only Select, Having and Order by clauses.

For more information about Grouping.

If the column is not in that grouping set the results is -1.

Example

0: Means it is part of the grouping set.

1: Means it is not part of the grouping set.

SELECT DEPT,GROUPING(DEPT) GRP_DEPT,YEAR(DOB) [YEAR],GROUPING(YEAR(DOB)) GRP_YEAR ,COUNT(*) [COUNT] FROM DBO.EMP
GROUP BY ROLLUP ((DEPT), (YEAR(DOB)))

Examples of Actual Scenarios

The following is for a salary by department:

SELECT
DEPT,
SUM(SALARY) AS [SALARY]
FROM
EMP
GROUP BY
DEPT WITH ROLLUP --OR--
SELECT
DEPT,
SUM(SALARY) AS [SALARY]
FROM
EMP
GROUP BY
ROLLUP(DEPT)