SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.


Filed under: Blog Tagged: SQLNewBlogger, syndicated, T-SQL

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...