Sql server® 2012 t-sql fundamentals

The 

GROUPING SETS

 Subclause

The GROUPING SETS subclause is a powerful enhancement to the GROUP BY clause that is used 

mainly in reporting and data warehousing. By using this subclause, you can define multiple grouping 

sets in the same query. Simply list the grouping sets that you want to define, separated by commas 

within the parentheses of the GROUPING SETS subclause, and for each grouping set list the members 

separated by commas within parentheses. For example, the following query defines four grouping 

sets: (empid, custid), (empid), (custid), and ().

SELECT empid, custid, SUM(qty) AS sumqty 

FROM dbo.Orders 

GROUP BY 

  GROUPING SETS 

  ( 

    (empid, custid), 

    (empid), 

    (custid), 

    () 

  );

This query is a logical equivalent of the previous solution that unified the result sets of four ag-

gregate queries, returning the same output. This query, though, has two main advantages over the 

previous solution—obviously it requires much less code, and SQL Server will optimize the number of 

times it scans the source table and won’t necessarily scan it separately for each grouping set.