Sql server® 2012 t-sql fundamentals

Grouping Sets

This section describes both what grouping sets are and the features in SQL Server that support 

grouping sets.

A grouping set is simply a set of attributes by which you group. Traditionally in SQL, a single ag-

gregate query defines a single grouping set. For example, each of the following four queries defines 

a single grouping set.

SELECT empid, custid, SUM(qty) AS sumqty 

FROM dbo.Orders 

GROUP BY empid, custid; 

 

SELECT empid, SUM(qty) AS sumqty 

FROM dbo.Orders 

GROUP BY empid; 

 

SELECT custid, SUM(qty) AS sumqty 

FROM dbo.Orders 

GROUP BY custid; 

 

SELECT SUM(qty) AS sumqty 

FROM dbo.Orders;

The first query defines the grouping set (empid, custid); the second (empid), the third (custid), and 

the last query define what’s known as the empty grouping set, (). This code returns four result sets—

one for each of the four queries.

www.it-ebooks.info

---