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

MariaDB TX, proven in production and driven by the community, is a complete database solution for any and every enterprise — a modern database for modern applications.

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.

MariaDB AX is an open source database for modern analytics: distributed, columnar and easy to use.

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

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}