Group By Grouping Sets

Group By Grouping Sets

Introduction

In this article we are trying to discuss about a special feature of T-SQL called Group By Grouping Sets which is introduced in Microsoft SQL Server 2008. It is used to specify multiple Grouping within a single Query. To understand it let take an example.

What we want to do

In this scenario we have a table like this.

EMPNAME	EMPSTATE	DEPARTMENT	SALARY
Joydeep Das	Tripura	IT	2000
Chandan Banerjee	West Bengal	IT	4000
Rajeev Kummar	Tripura	IT	2000
Vivek Singh	West Bengal	Accounts	5000
Darshit Triwari	West Bengal	Accounts	4000

We want the Output like this

EMPNAME	EMPSTATE	DEPARTMENT	SALARY
Joydeep Das	Tripura	IT	2000
Chandan Banerjee	West Bengal	IT	4000
Rajeev Kummar	Tripura	IT	2000
Vivek Singh	West Bengal	Accounts	5000
Darshit Triwari	West Bengal	Accounts	4000
	West Bengal	Accounts	9000
	Tripura	IT	4000
	West Bengal	IT	4000
		Accounts	9000
		IT	8000

Hope you understand the scenario.

How we can Implement it

Step-1 [ Making the Base Table ]

IF OBJECT_ID(N'[dbo].[tbl_STATEWISEEMPSAL]', N'U')IS NOT NULL

   BEGIN

       DROP TABLE [dbo].[tbl_STATEWISEEMPSAL];

   END

GO

CREATE TABLE [dbo].[tbl_STATEWISEEMPSAL]

   (

     EMPNAME     VARCHAR(50)   NOT NULL,

       EMPSTATE    VARCHAR(50)   NOT NULL,

       DEPARTMENT  VARCHAR(50)   NOT NULL,

       SALARY      DECIMAL(18,2) NOT NULL

   );

GO

Step-2 [ Inserting Records into Base Table ]

INSERT INTO [dbo].[tbl_STATEWISEEMPSAL]

       (EMPNAME, EMPSTATE, DEPARTMENT, SALARY)

VALUES ('Joydeep Das', 'Tripura', 'IT', 2000),

       ('Chandan Banerjee', 'West Bengal', 'IT', 4000),

       ('Rajeev Kumar', 'Tripura', 'IT', 2000),

       ('Vivek Singh', 'West Bengal', 'Accounts', 5000),

       ('Darshir Tiwari', 'West Bengal', 'Accounts', 4000);

GO

Step-3 [ Using UNION ALL Statement ]

SELECT EMPNAME, EMPSTATE, DEPARTMENT, SUM(SALARY) SALARY

FROM   [dbo].[tbl_STATEWISEEMPSAL]

GROUP BY EMPNAME, EMPSTATE, DEPARTMENT

UNION ALL

SELECT NULL EMPNAME, EMPSTATE, DEPARTMENT, SUM(SALARY) SALARY

FROM   [dbo].[tbl_STATEWISEEMPSAL]

GROUP BY EMPSTATE, DEPARTMENT

UNION ALL

SELECT NULL EMPNAME, NULL EMPSTATE, DEPARTMENT, SUM(SALARY) SALARY

FROM   [dbo].[tbl_STATEWISEEMPSAL]

GROUP BY DEPARTMENT;

Step-4 [ Using Microsoft SQL 2008 Group By Grouping Set ]

SELECT EMPNAME, EMPSTATE, DEPARTMENT, SUM(SALARY) SALARY

FROM   [dbo].[tbl_STATEWISEEMPSAL]

GROUP BY GROUPING SETS ((EMPNAME, EMPSTATE, DEPARTMENT), (EMPSTATE),(DEPARTMENT))

ORDER BY 1 DESC;

Hope you like it.