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

Check out the IT Market Clock report for recommendations on how to consolidate and replace legacy databases. Brought to you in partnership with MariaDB.

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.

Interested in reducing database costs by moving from Oracle Enterprise to open source subscription?  Read the total cost of ownership (TCO) analysis. Brought to you in partnership with MariaDB.

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.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}