Home > SQL > SQL: With Rollup

SQL: With Rollup

November 9th, 2009 Jonathan Moore Leave a comment Go to comments

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.

How does the really change what I’m doing? Well, let’s take a look at how I currently handle a situation and how it changes with With Rollup.

Four friends have decided to go out bowling and want to keep their score across four games. At the end of the night, they want to know their Total Score, Highest Score, Lowest Score and Average Score across all four games. They want these by each person, but for the group as well. Paul goes with the group, but doesn’t actually bowl any while he’s there. This following is the data to show how they bowled and will be used in the examples that follow.


DECLARE @Player TABLE(PlayerID INT IDENTITY, Name VARCHAR(30))

INSERT INTO @Player VALUES (’Chris’)
INSERT INTO @Player VALUES (’Sam’)
INSERT INTO @Player VALUES (’Alex’)
INSERT INTO @Player VALUES (’Paul’)

DECLARE @Scores TABLE(ScoreID INT IDENTITY, PlayerID INT, Score BIGINT)

INSERT INTO @Scores VALUES (3, 170)
INSERT INTO @Scores VALUES (1, 223)
INSERT INTO @Scores VALUES (3, 187)
INSERT INTO @Scores VALUES (1, 250)
INSERT INTO @Scores VALUES (2, 195)
INSERT INTO @Scores VALUES (1, 244)
INSERT INTO @Scores VALUES (3, 148)
INSERT INTO @Scores VALUES (3, 166)
INSERT INTO @Scores VALUES (2, 195)
INSERT INTO @Scores VALUES (1, 237)
INSERT INTO @Scores VALUES (2, 212)
INSERT INTO @Scores VALUES (2, 172)

The simple way that I’d handle this in the past is just joining these values together and then dumping it out to the application.


SELECT 'PlayerName' = PLY.Name, SCR.Score
FROM @Player PLY
LEFT OUTER JOIN @Scores SCR
ON PLY.PlayerID = SCR.PlayerID
ORDER BY PLY.PlayerID
Chris 223
Chris 250
Chris 244
Chris 237
Sam 195
Sam 195
Sam 212
Sam 172
Alex 170
Alex 187
Alex 148
Alex 166
Paul NULL

The application would then figure out the Total, High, Low and Average Score for each person as well as the group. For this, we’d need 10 different variables (also would need the count so that we could average correctly) and each calculation is prone to mistakes in coding. The application also has to compare current user to the user from the last record to know when to put in summary rows. To make life easier, we could just group the items together and return that after returning our detail records.


SELECT 'PlayerID' = PLY.PlayerID
, ‘PlayerName’ = (SELECT P2.Name FROM @Player P2 WHERE P2.PlayerID = PLY.PlayerID)
, ‘TotalScore’ = SUM(SCR.Score), ‘HighScore’ = MAX(SCR.Score), ‘LowScore’ = MIN(SCR.Score)
, ‘AverageScore’ = AVG(CONVERT(MONEY,SCR.Score))
FROM @Player PLY
LEFT OUTER JOIN @Scores SCR
ON PLY.PlayerID = SCR.PlayerID
GROUP BY PLY.PlayerID
ORDER BY PLY.PlayerID
PlayerId PlayerName TotalScore HighScore LowScore AverageScore
1 Chris 954 250 223 238.5
2 Sam 774 212 172 193.5
3 Alex 671 187 148 167.67
4 Paul NULL NULL NULL NULL


SELECT 'PlayerID' = NULL, 'PlayerName' = 'Total', 'TotalScore' = SUM(SCR.Score)
, ‘HighScore’ = MAX(SCR.Score), ‘LowScore’ = MIN(SCR.Score)
, ‘AverageScore’ = AVG(CONVERT(MONEY,SCR.Score))
FROM @Player PLY
LEFT OUTER JOIN @Scores SCR
ON PLY.PlayerID = SCR.PlayerID

This way, we’ve gotten rid of calculations in the application, but now our application has 3 different sets of data. It also has to know when to use which set of data.
With Rollup simplifies all of this by allowing detail and summary data in the same set.

PlayerId PlayerName TotalScore HighScore LowScore AverageScore
NULL Total 2399 250 148 199.9166

SELECT 'PlayerID' = PLY.PlayerID
, ‘PlayerName’ = (SELECT P2.Name FROM @Player P2 WHERE P2.PlayerID = PLY.PlayerID)
, SCR.ScoreID, ‘TotalScore’ = SUM(SCR.Score), ‘HighScore’ = MAX(SCR.Score)
, ‘LowScore’ = MIN(SCR.Score), ‘AverageScore’ = AVG(CONVERT(MONEY,SCR.Score))
FROM @Player PLY
LEFT OUTER JOIN @Scores SCR
ON PLY.PlayerID = SCR.PlayerID
GROUP BY PLY.PlayerID, SCR.ScoreID
WITH ROLLUP

PlayerId PlayerName ScoreID TotalScore HighScore LowScore AverageScore
1 Chris 2 223 223 223 223
1 Chris 4 250 250 250 250
1 Chris 6 244 244 244 244
1 Chris 10 237 237 237 237
1 Chris NULL 954 250 223 238.5
2 Sam 5 195 195 195 195
2 Sam 9 195 195 195 195
2 Sam 11 212 212 212 212
2 Sam 12 172 172 172 172
2 Sam NULL 774 212 172 193.5
3 Alex 1 170 170 170 170
3 Alex 3 187 187 187 187
3 Alex 7 148 148 148 148
3 Alex 8 166 166 166 166
3 Alex NULL 671 187 148 167.67
4 Paul NULL NULL NULL NULL NULL
4 Paul NULL NULL NULL NULL NULL
NULL NULL NULL 2399 250 148 199.9166

As we see with the data returned, there are some rows with a NULL ScoreID and one row with both a NULL ScoreID and a NULL PlayerID. The rows with a NULL ScoreID are summary rows that contains the aggregates of all the scores for a player. The row with both a NULL ScoreID and a NULL PlayerID is the summary row that contains the aggregates for all scores for all players.
However, this means that we are relying on NULL comparisons to find which rows are summary rows. To make this an easier check, we can add the GROUPING clause.


SELECT 'PlayerID' = PLY.PlayerID
, ‘PlayerName’ = (SELECT P2.Name FROM @Player P2 WHERE P2.PlayerID = PLY.PlayerID)
, ‘ScoreGroup’ = GROUPING(SCR.ScoreID), ‘PlayerGroup’ = GROUPING(PLY.PlayerID)
, ‘TotalScore’ = SUM(SCR.Score), ‘HighScore’ = MAX(SCR.Score), ‘LowScore’ = MIN(SCR.Score)
, ‘AverageScore’ = AVG(CONVERT(MONEY,SCR.Score))
FROM @Player PLY
LEFT OUTER JOIN @Scores SCR
ON PLY.PlayerID = SCR.PlayerID
GROUP BY PLY.PlayerID, SCR.ScoreID
WITH ROLLUP

PlayerId PlayerName ScoreGroup PlayerGroup TotalScore HighScore LowScore AverageScore
1 Chris 0 0 223 223 223 223
1 Chris 0 0 250 250 250 250
1 Chris 0 0 244 244 244 244
1 Chris 0 0 237 237 237 237
1 Chris 1 0 954 250 223 238.5
2 Sam 0 0 195 195 195 195
2 Sam 0 0 195 195 195 195
2 Sam 0 0 212 212 212 212
2 Sam 0 0 172 172 172 172
2 Sam 1 0 774 212 172 193.5
3 Alex 0 0 170 170 170 170
3 Alex 0 0 187 187 187 187
3 Alex 0 0 148 148 148 148
3 Alex 0 0 166 166 166 166
3 Alex 1 0 671 187 148 167.67
4 Paul 0 0 NULL NULL NULL NULL
4 Paul 1 0 NULL NULL NULL NULL
NULL NULL 1 1 2399 250 148 199.9166

As you can now see, we now have 2 columns that contain a bit type to determine whether or not the row is a summary row. When the ScoreGroup is 1, you can expect this row to be a summary of scores for the group. If PlayerGroup is also 1, then the scores are summarized for all the scores for the all the players. We now have one set of data to work with and can easily output it from the application without having to do the summaries, keep up with current users or worry about how many records are in the set.
With Rollup allows for SQL to handle a lot of the dirty work that I used to handcode. Using SQL for this makes code cleaner and more consistent and just plain simpler.

Categories: SQL Tags: , , , ,
  1. No comments yet.
  1. No trackbacks yet.