I don’t think ROLLUP has a great use in the real world (feel free to comment below if you have a real use for it). The best I can see is that you have SQL Server emailing the results of a query to someone and they want subtotals. Adding totals can be done by any reporting app, probably more efficiently than SQL will do it, and it’s probably easier to debug there as well.
I do, however, think CUBE has an amazing purpose.
The funny thing is, I disagree and find
ROLLUP a lot more useful than
ROLLUP gives me aggregations along a hierarchy. If I set up my
GROUP BY clause correctly, I can see the results of my Product Category –> Product Subcategory –> Product hierarchy quite easily. Yeah, I can also do this in reporting, but if I just need a quick printout or need to do something special with the subtotals,
ROLLUP is great. I definitely use
GROUPING SETS more than
CUBE combined, but I’ve had production code use
ROLLUP and, to the best of my recollection, never have I created a procedure or report which is based off of