It is possible to have different grouping sets in a single query. For example in a single query we could have a total sales value by year, month, date or we could count the total number of our customers by city, state or country in one query. We could achieve that by using GROUPING SETS, CUBE and ROLLUP clauses.
ROLLUP gives an aggregate value by using the order of columns in a GROUP BY clause. ROLL UP will pass one record at a time while CUBE returns aggregate value of all possible combinations of column in the GROUP BY clause. GROUPING SETS give us more flexibility that we can’t do both on ROLLUP and CUBE. Using GROUPING SETS we could aggregate our data in any selected sets we want.
Please follow the link above to learn more about ROLLUP, CUBE and GROUPING SETS.
Read More
http://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx
