Sql server® 2012 t-sql fundamentals

The 

CUBE

 Subclause

The CUBE subclause of the GROUP BY clause provides an abbreviated way to define multiple group-

ing sets. In the parentheses of the CUBE subclause, you provide a list of members separated by 

commas, and you get all possible grouping sets that can be defined based on the input members. For 

example, CUBE(a, b, c) is equivalent to GROUPING SETS( (a, b, c), (a, b), (a, c), (b, c), (a), (b), (c), () ). In 

set theory, the set of all subsets of elements that can be produced from a particular set is called the 

power set. You can think of the CUBE subclause as producing the power set of grouping sets that can 

be formed from the given set of elements.

Instead of using the GROUPING SETS subclause in the previous query to define the four group-

ing sets (empid, custid), (empid), (custid), and (), you can simply use CUBE(empid, custid). Here’s the 

complete query.

SELECT empid, custid, SUM(qty) AS sumqty 

FROM dbo.Orders 

GROUP BY CUBE(empid, custid);

www.it-ebooks.info

---