You know everything about GROUP BY. Do you?

SQL is a stout language and SQL Server has so many features that it’s impossible to be an expert in everything. I see a lot of people gravitate towards performance; but, that’s not the best place to focus. If you don’t know the fundamentals of the language then you will never be the best performance tuner.

Today we’re going to look at GROUPING SETS. This is a neat feature that I have never seen anyone use. Perhaps because it’s value is limited for any application purposes where you can simply group in a report or data grid. Where I think I would have seen people using this is for admin and discovery related tasks.

Let’s say that your manager wants to know how much space is used per volume and by data file type. How would you proceed? One simple way is to query sys.master_files cut & paste the data to Excel. From there you’d need to do a few more operations and you’d have your report. Another way may be to use REPORT BUILDER to quickly generate a report. But both of these are much slower than just using SQL to get the groupings.

Here’s a quick example to get a total usage in MB by file type for each volume (when not using mount points):

SELECT type_desc as FileType,
LEFT(physical_name, 1) as Volume,
SUM(size*8/1024) as [Size MB]
                                           (type_desc, left(physical_name, 1)),
                                           (left(physical_name, 1))

Using GROUPING SETS gives us a subtotal for each grouping set. Here’s the result:


You could also use CUBE but the result would include an additional subtotal, which may be helpful.

SELECT type_desc as FileType, left(physical_name, 1) as Volume,
SUM(size*8/1024) as [Size MB]
(type_desc, left(physical_name, 1))


One thought on “You know everything about GROUP BY. Do you?

  1. Adding the grouping set ,() after (left(physical_name, 1)) will also give the additional sub total that cube gives.


Leave a Reply

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

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

Facebook photo

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

Connecting to %s