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


This was originally created in 2011/2012 in a series I dubbed "SQL Tips" for my team (via email and an internal blog site). I've decided to add these SQL Tips to my external blog. These are being posted exactly how they were written ~6 years ago...unless I found someone's name being called out...I may have edited that to protect the innocent. 🙂

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
Output:
Assigned Site
Number of Clients
RD3
68626
NA1
34477
SVC
1
EU1
40534
RD2
64834
 
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
Output:
Assigned Site
Number of Clients
EU1
40535
NA1
34477
RD2
64822
RD3
68642
SVC
1
NULL
208477
 
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
Output:
Assigned Site
Is_Virtual_Machine0
Number of Clients
EU1
NULL
356
RD2
NULL
1059
EU1
0
34686
RD2
0
50304
EU1
1
5494
RD2
1
13385
 
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
Output:
Assigned Site
Is_Virtual_Machine0
Number of Clients
EU1
NULL
357
RD2
NULL
1053
NULL
NULL
1410
EU1
0
34688
RD2
0
50216
NULL
0
84904
EU1
1
5497
RD2
1
13387
NULL
1
18884
NULL
NULL
105198
EU1
NULL
40542
RD2
NULL
64656
 
 
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
Output:
Assigned Site
Is_Virtual_Machine0
Number of Clients
EU1
NULL
357
EU1
0
34688
EU1
1
5497
EU1
NULL
40542
RD2
NULL
1053
RD2
0
50200
RD2
1
13386
RD2
NULL
64639
NULL
NULL
105181
 
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 <composite element list>.
ROLLUP() = The number of groupings that is returned equals the number of expressions in the <composite element list> 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
Experiment with these and you’ll quickly learn how to use them to your advantage!
Comments (0)

Skip to main content