SQL Tip: Creating a Grand Total (and additional subtotals)

[edit: April 2019] This was originally posted to my internal (to Microsoft) blog on Jan 23, 2012 as part of a series I called ‘SQL Tips’ for coworkers and an email distribution list people could sign up to. I have left this as originally written but have updated formatting for my WordPress theme.

Sometimes when you write a query to find some totals by a particular category you’d also like to see the grand total. If your query is being used in a reporting services report then this is easily achieved within the report. However, if you’re just writing a T-SQL query to find this data you’ll be interested in some ‘GROUP BY’ functions. In this SQL Tip we’ll look at the CUBE & ROLLUP functions.

Let’s create a simple ‘sub-total’ query:

SELECT  sit.SMS_Assigned_Sites0 AS [Assigned Site]
       ,COUNT(sis.ResourceID) AS [Number of Clients]
  FROM dbo.v_R_System_valid sis
       INNER JOIN dbo.v_RA_System_SMSAssignedSites sit
          ON sis.ResourceID = sit.ResourceID
 GROUP BY sit.SMS_Assigned_Sites0;
GO

The ‘simple’ grand total (CUBE or ROLLUP):

SELECT  sit.SMS_Assigned_Sites0 AS [Assigned Site]
       ,COUNT(sis.ResourceID)   AS [Number of Clients]
  FROM dbo.v_R_System_valid sis
       INNER JOIN dbo.v_RA_System_SMSAssignedSites sit
          ON sis.ResourceID = sit.ResourceID
 GROUP BY CUBE (sit.SMS_Assigned_Sites0); -- In this example "ROLLUP" would work exactly the same
GO

In the ‘simple’ subtotal query using the CUBE or ROLLUP function will do the same thing: create one additional record – the “total” record. You’ll notice that it shows this with a “NULL” in the ‘Assigned Site’ column. You can use the ISNULL function to specify that when NULL is found replace it with “Total”. However, this does assume that there are no NULLs to be found in the assigned site column; because if there are you’ll have two records that say “total”.

What does this function look like in queries where there are two (or more) columns to GROUP BY? To do this we will add a column to our original query (and limit the results to only two sites for simplicity sake.

More columns for subtotals:

SELECT  sit.SMS_Assigned_Sites0 AS [Assigned Site]
       ,sis.Is_Virtual_Machine0
       ,COUNT(sis.ResourceID)   AS [Number of Clients]
  FROM dbo.v_R_System_valid sis
       INNER JOIN dbo.v_RA_System_SMSAssignedSites sit
          ON sis.ResourceID = sit.ResourceID
         AND sit.SMS_Assigned_Sites0 IN (N'EU1',N'RD2') -- Add this to show fewer records for the example
 GROUP BY  sit.SMS_Assigned_Sites0
          ,sis.Is_Virtual_Machine0;
GO

Adding the “Is_Virtual_Machine0” column not only adds another ‘subtotal’ but also presents us with some NULLs before we even look at the rollups. We all know how to read this output: EU1 has 356 clients that don’t have a value for the virtual machine field, 34686 clients that are designated as NOT being a virtual machine, and 5494 clients designated as virtual machines. Now let’s add some totals to this output and see if we can make sense of it.

More subtotal columns and the CUBE function:

SELECT  sit.SMS_Assigned_Sites0 AS [Assigned Site]
       ,sis.Is_Virtual_Machine0
       ,COUNT(sis.ResourceID)   AS [Number of Clients]
  FROM dbo.v_R_System_valid sis
       INNER JOIN dbo.v_RA_System_SMSAssignedSites sit
          ON sis.ResourceID = sit.ResourceID
         AND sit.SMS_Assigned_Sites0 IN (N'EU1',N'RD2') -- Add this to show fewer records for the example
 GROUP BY CUBE ( sit.SMS_Assigned_Sites0
                ,sis.Is_Virtual_Machine0
                );
GO

More subtotal columns and the ROLLUP function:

SELECT  sit.SMS_Assigned_Sites0 AS [Assigned Site]
       ,sis.Is_Virtual_Machine0
       ,COUNT(sis.ResourceID)   AS [Number of Clients]
  FROM dbo.v_R_System_valid sis
       INNER JOIN dbo.v_RA_System_SMSAssignedSites sit
          ON sis.ResourceID = sit.ResourceID
         AND sit.SMS_Assigned_Sites0 IN (N'EU1',N'RD2') -- Add this to show fewer records for the example
 GROUP BY ROLLUP ( sit.SMS_Assigned_Sites0
                  ,sis.Is_Virtual_Machine0
                  );
GO

I’ve highlighted the totals (or additional subtotals) added by the CUBE and ROLLUP functions. You’ll notice there are some differences. This is where Books Online actually does a decent job explaining the difference between the two:
CUBE() = CUBE outputs a grouping for all permutations of expressions in the .
ROLLUP() = The number of groupings that is returned equals the number of expressions in the plus one [the grand total].

Thus, we can see with our example that the CUBE created a summary for each “permutation”: a grouping (or subtotal) for all NULL, 1, and 0 values in the virtual machine column regardless of site; a grouping (or subtotal) for each site regardless of the virtual machine property; and one grand total. Whereas, ROLLUP only created the grouping (or subtotal) for each site regardless of the virtual machine property; and one grand total.

You can, therefore, define which groupings to output based on which columns you add in the parentheses for the CUBE or ROLLUP function (aka the “composit element list” or “cube/rollup spec”). You can have more than one ROLLUP if desired; for example you could do something like the following:

 GROUP BY  ROLLUP (sit.SMS_Assigned_Sites0)
          ,ROLLUP (sis.Is_Virtual_Machine0);
GO

Advertisement