If you use GROUP BY Product_Name,Scheme_Name WITH ROLLUP (sorry for using the deprecated syntax; I don't have a Books Online 2008 at hand to check the new syntax), you get these rows:

* One for each unique Product_Name, Scheme_Name combination.

* One for each unique Product_Name (for the totals over all schemes for that product)

* One for the grand total

If you reverse it to GROUP BY Scheme_Name, Product_Name WITH ROLLUP, you will lose the rows with the totals per product over all schemes, but instead you will get rows with the totals per scheme over all products.

If you use WITH CUBE instead of WITH ROLLUP, you will get all these variations.

So basically, the WITH CUBE produces all subtotals over zero, one or more of the GROUP BY columns, and the WITH ROLLUP reduces that to only subtotals over no GROUP BY columnss, the first only, first and second, first to third, etc.