ROLLing UP Totals–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was editing an article recently that talked about ROLLUP, and I wanted to play with this a bit more. I hadn’t used this much in my career, but it’s a handy operator that’s worth including in your toolkit.

ROLLUP is used to provide additional totals for your aggregates while using GROUP BY. Here’s a little example. I’ve got some data for American Football quaterbacks. In this case, I’ve extracted some stats for a few noteworthy players today. Here’s a sample:

2017-10-16 13_05_05-SQLQuery1.sql - (local)_SQL2016.NFLAnalysis (PLATO_Steve (74))_ - Microsoft SQL

I want to aggregate some data together. For example, let’s say that we want to look at the total touchdowns scored per year by these players in their first 5 years. That’s a simple query:

SELECT
   Season = qs.CareerYear
   , TDs = SUM(qs.Touchdowns)
  FROM dbo.QBStats AS qs
  WHERE qs.CareerYear <= 5
GROUP BY qs.CareerYear

This gives me data like this:

2017-10-16 13_06_28-SQLQuery1.sql - (local)_SQL2016.NFLAnalysis (PLATO_Steve (74))_ - Microsoft SQL

However, when I look at this, I don’t have a total across all years. In my set, there are players that have played up to 18 seasons, and I want to know the aggregate number of TDs. I can get that by adding WITH ROLLUP. This is added

2017-10-16 13_07_25-SQLQuery1.sql - (local)_SQL2016.NFLAnalysis (PLATO_Steve (74))_ - Microsoft SQL

after the group by. However, I can also change this to GROUP BY ROLLUP and include the columns in parenthesis.

SELECT
   Season = qs.CareerYear
   , TDs = SUM(qs.Touchdowns)
  FROM dbo.QBStats AS qs
  WHERE qs.CareerYear <= 5
  GROUP BY ROLLUP (qs.CareerYear)

This is OK, but the NULL isn’t great. What can I do here? I can add an ISNULL or COALESCE to my query and get this:

2017-10-16 13_10_04-SQLQuery1.sql - (local)_SQL2016.NFLAnalysis (PLATO_Steve (74))_ - Microsoft SQL

Useful, but what about if I’m aggregating by individual players? I can add the player to the column list, but I also need to add the player to the ROLLUP (or GROUP BY) list as well. If I do that, I get this:

2017-10-16 13_11_40-SQLQuery1.sql - (local)_SQL2016.NFLAnalysis (PLATO_Steve (74))_ - Microsoft SQL

That’s quite a bit more data, and I now see NULL values in the Player name. What’s this?

These are the totals for that subgroup. In this case, the first NULL above, next to the 1, is the total for all players for season 1. I can use another trick to clean this up:

2017-10-16 13_14_33-SQLQuery1.sql - (local)_SQL2016.NFLAnalysis (PLATO_Steve (74))_ - Microsoft SQL

This now shows some data in an easier to understand fashion, with some totals. We can see for season 1 that 3 players really started their careers in a strong fashion and 3 didn’t.

At the end, however, we get a different result.

2017-10-16 13_17_09-SQLQuery1.sql - (local)_SQL2016.NFLAnalysis (PLATO_Steve (74))_ - Microsoft SQL

This is because we have handled the player name as a NULL for the second column, but not the season as a NULL. For this last row, out “Total” text comes from an ISNULL of the first column. Here we’d want to do this:

2017-10-16 13_18_30-SQLQuery1.sql - (local)_SQL2016.NFLAnalysis (PLATO_Steve (74))_ - Microsoft SQL

This is where COALESCE  comes in handy, allowing us to account for multiple NULL values.

ROLLUP is a nice way to get totals for each of your GROUP BY columns. This can make some of your reporting easier, and perhaps even faster by having the totals calculated in the result set.

SQLNewBlogger

This was a fairly easy piece to write. It took longer to get data together and find a set that had two grouping elements and was interesting than write the queries. I also had to play with ROLLUP a bit to clean up the NULLs, which always made this seem like a useless operator.

I think I’d look to use this more if I had the chance for many reports, as summing totals in some client tools is a pain.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

2 Responses to ROLLing UP Totals–#SQLNewBlogger

  1. Terra says:

    Skype has launched its internet-centered buyer beta towards the entire world,
    after starting it generally from the U.S. and You.K.
    previous this calendar month. Skype for Internet also now works with Chromebook and Linux for instant text messaging interaction (no
    video and voice nevertheless, all those demand a connect-in installment).

    The expansion from the beta brings help for an extended list of spoken languages to help you
    strengthen that global functionality

  2. Tom Warren says:

    Good article. I too believe I could use ROLLUP more in reporting. Thanks for the walk-through!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s