title: When to use GROUPING SETS, CUBE and ROLLUP
body: <p>I have recently learned about GROUPING SETS, CUBE and ROLLUP for defining multiple grouping sets in sql server.</p>
  
  <p>What I am asking is under what circumstances do we use these features ? What are the benefits and advantages of using them?</p>
  
  <pre><code>SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders
  FROM Sales.Orders
  GROUP BY GROUPING SETS ( ( shipperid, YEAR(shippeddate) ), ( shipperid ), ( YEAR(shippeddate) ), ( ) );
  
  
  SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders
  FROM Sales.Orders
  GROUP BY CUBE( shipperid, YEAR(shippeddate) );
  
  
  SELECT shipcountry, shipregion, shipcity, COUNT(*) AS numorders
  FROM Sales.Orders
  GROUP BY ROLLUP( shipcountry, shipregion, shipcity );
  </code></pre>