Indeed, I had forgotten about ROLLUP. When using it in the past (probably not ROLLUP but some other, earlier, New Feature in a, now, old version of SQL) we had trouble getting values in columns, used for grouping sub-totals, to display in the report. (Was there something that used to, also, output rows with "====" perhaps ... ?) I tried GB's example on some data here and couldn't see any problem with it, so whatever memory I have is either wrong, or that issue has long since gone away with improved syntax / features
We typically have more complexity than just column totals though (and not uncommon, of course for an O/P's example to be a watering-down of the actual problem ...). For example, I currently have a need to subtotal on the Time Worked by people on a project, including sub-totals for the person running the report and "everyone else". That also requires a Total Time to be shown ... but that column is stored by the APP as DATETIME, so I can't total it without converting to Minutes, and back again).
Perhaps I could do those things with ROLLUP ... I really ought to check!
GROUP BY has always had some pretty powerful stuff in it including WITH ROLLUP and WITH CUBE (which shows additional levels compared to WITH ROLLUP) and a function called GROUPING, which allows you to do some pretty amazing stuff with controlling what is displayed for group breaks. As of 2005, they also came out with GROUPING SETS, which allows exquisite control over how to group things. They also came out with GROUPING_ID, which is basically the same as GROUPING but allows you to control the grouping using binary bit masking to really cut down on the amount of GROUPING code that you might need to write.
Here are the links on those goodies. Their examples leave a whole lot to be desired so, if you have something special that you'd like to see, post your example data back and what you'd like to see and I'll see if I can come up with some code to do it for you.
for an O/P's example to be a watering-down of the actual problem ...). For example, I currently have a need to subtotal on the Time Worked by people on a project, including sub-totals for the person running the report and "everyone else". That also requires a Total Time to be shown ... but that column is stored by the APP as DATETIME, so I can't total it without converting to Minutes, and back again).