Question: I have recently learned about GROUPING SETS, CUBE and ROLLUP for defining multiple grouping sets in sql server.

What I am asking is under what circumstances do we use these features ? What are the benefits and advantages of using them?

```
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 );

```

Answer: 
Firstly, for those who haven't already read up on the subject:

* [Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS](http://technet.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx)

That being said, don't think about these grouping options as ways to get a result set. **These are performance tools**.

Let's take `ROLLUP` as a simple example.

I can use the following query to get the count of records for each value of GrpCol.

```
SELECT   GrpCol, count(*) AS cnt
FROM     dbo.MyTable
GROUP BY GrpCol

```

And I can use the following query to summarily "roll up" the count of ALL records.

```
SELECT   NULL, count(*) AS cnt
FROM     dbo.MyTable

```

And I could `UNION ALL` the above two queries to get the exact same results I might get if I had written the first query with the `ROLLUP` clause (that's why I put the NULL in there).

It might actually be more convenient for me to execute this as two different queries because then I have the grouped results separate from my totals. Why would I want my final total mixed right in to the rest of those results? The answer is that doing both together using the `ROLLUP` clause is more efficient. SQL Server will use an execution plan that calculates all of the aggregations together in one pass. Compare that to the `UNION ALL` example which would provide the exact same results but use a less efficient execution plan (two table scans instead of one).

Imagine an extreme example in which you are working on a data set so large that each scan of the data takes one whole hour. You have to provide totals on basically every possible dimension (way to slice) that data every day. Aha! I bet one of these grouping options is exactly what you need. If you save off the results of that one scan into a special schema layout, you will then be able to run reports for the rest of the day off the saved results.

So I'm basically saying that you're working on a data warehouse project. For the rest of us it mostly falls into the "neat thing to know" category.
I find they are good when you're producing a report and the result is not something which can be rolled up within the client.

For example, if you're doing something with `COUNT(DISTINCT...)` then the result across a larger group is not necessarily the same value as the sum of the parts. Eg, across two individual days you might have 1500 visitors and 2000 visitors, but the total could be anywhere between 2000 and 3500, depending on the overlap. It's nice to do this in the client, but because the client can't tell what the overlap is, you can use `GROUPING SETS` to provide the answer (and then handle that extra row coming through in the report).