SQL: With Rollup
There have been many cases over the years where I have had to spew out data into some type of output for the user, from Crystal Reports to text files and straight output to a print source. Most of the time, the users wanted multiple data rows and then a summary row to go along with it.
What that usually meant though was that I returned the data to the application and then as I was printing I would do any calculations that needed to be done (Sum / Max / Min / Average) and also have to look for the breaks between groups so I could show subtotals as well. This meant for every aggregate function I wanted to do, I had to have a corresponding Subtotal and Grand Total variable and continuously calculate the variables for each record.
Seems that I have been doing a lot more work than I needed to. With Rollup takes away a lot of the applications need to manipulate the data so that all you really have to do is display the output in the format the user is expecting. With Rollup allows the data to not only have the detail records, but summary records to go with those records.
Read more…