Sql server® 2012 t-sql fundamentals

 

CHAPTER 7

 

Beyond the Fundamentals of Querying

 

 235

The 

ROLLUP

 Subclause

The ROLLUP subclause of the GROUP BY clause also provides an abbreviated way to define multiple 

grouping sets. However, unlike the CUBE subclause, ROLLUP doesn’t produce all possible group-

ing sets that can be defined based on the input members—it produces a subset of those. ROLLUP 

assumes a hierarchy among the input members and produces all grouping sets that make sense 

considering the hierarchy. In other words, whereas CUBE(a, b, c) produces all eight possible grouping 

sets from the three input members, ROLLUP(a, b, c) produces only four grouping sets, assuming the 

hierarchy a>b>c, and is the equivalent of specifying GROUPING SETS( (a, b, c), (a, b), (a), () ).

For example, suppose that you want to return total quantities for all grouping sets that can be de-

fined based on the time hierarchy order year > order month > order day. You could use the GROUPING 

SETS subclause and explicitly list all four possible grouping sets.

GROUPING SETS( 

  (YEAR(orderdate), MONTH(orderdate), DAY(orderdate)), 

  (YEAR(orderdate), MONTH(orderdate)), 

  (YEAR(orderdate)), 

  () )

The logical equivalent that uses the ROLLUP subclause is much more economical.

ROLLUP(YEAR(orderdate), MONTH(orderdate), DAY(orderdate))

Here’s the complete query that you need to run.

SELECT  

  YEAR(orderdate) AS orderyear, 

  MONTH(orderdate) AS ordermonth, 

  DAY(orderdate) AS orderday, 

  SUM(qty) AS sumqty 

FROM dbo.Orders 

GROUP BY ROLLUP(YEAR(orderdate), MONTH(orderdate), DAY(orderdate));

This query produces the following output.

orderyear   ordermonth     orderday    sumqty 

----------- -------------- ----------- ----------- 

2007        4              18          22 

2007        4              NULL        22 

2007        8              2           10 

2007        8              NULL        10 

2007        12             24          32 

2007        12             NULL        32 

2007        NULL           NULL        64 

2008        1              9           40 

2008        1              18          14 

2008        1              NULL        54 

2008        2              12          12 

2008        2              NULL        12 

2008        NULL           NULL        66 

www.it-ebooks.info

---

236  

Microsoft SQL Server 2012 T-SQL Fundamentals

2009        2              12          10 

2009        2              16          20 

2009        2              NULL        30 

2009        4              18          15 

2009        4              NULL        15 

2009        9              7           30 

2009        9              NULL        30 

2009        NULL           NULL        75 

NULL        NULL           NULL        205