Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Group By Grouping Sets

DZone's Guide to

Group By Grouping Sets

Group By Grouping Sets is a special feature of T-SQL and is used to specify multiple Groupings within a single Query. To understand it let's take an example.

· Database Zone
Free Resource

Find out how Database DevOps helps your team deliver value quicker while keeping your data safe and your organization compliant. Align DevOps for your applications with DevOps for your SQL Server databases to discover the advantages of true Database DevOps, brought to you in partnership with Redgate

Introduction

In this article we are trying to discuss 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 Groupings within a single Query. To understand it let's 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.

Align DevOps for your applications with DevOps for your SQL Server databases to increase speed of delivery and keep data safe. Discover true Database DevOps, brought to you in partnership with Redgate

Topics:
sql ,sql server ,database ,t-sql ,group by grouping sets

Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}