- Subtotal versus total versus grandtotal how to#
- Subtotal versus total versus grandtotal plus#
- Subtotal versus total versus grandtotal series#
ROLLUP() = The number of groupings that is returned equals the number of expressions in the plus one. This is where Books Online actually does a decent job explaining the difference between the two:ĬUBE() = CUBE outputs a grouping for all permutations of expressions in the. You’ll notice there are some differences. I’ve highlighted the totals (or additional subtotals) added by the CUBE and ROLLUP functions. GROUP BY ROLLUP ( sit.SMS_Assigned_Sites0 More subtotal columns and the ROLLUP function: SELECT sit.SMS_Assigned_Sites0 AS More subtotal columns and the CUBE function: SELECT sit.SMS_Assigned_Sites0 AS Now let’s add some totals to this output and see if we can make sense of it.
Subtotal versus total versus grandtotal how to#
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. More columns for subtotals: SELECT sit.SMS_Assigned_Sites0 AS ĪND sit.SMS_Assigned_Sites0 IN (N'EU1',N'RD2') - Add this to show fewer records for the exampleĪdding the “Is_Virtual_Machine0” column not only adds another ‘subtotal’ but also presents us with some NULLs before we even look at the rollups. 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. 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”. You can use the ISNULL function to specify that when NULL is found replace it with “Total”. You’ll notice that it shows this with a “NULL” in the ‘Assigned Site’ column. In the ‘simple’ subtotal query using the CUBE or ROLLUP function will do the same thing: create one additional record – the “total” record. GROUP BY CUBE (sit.SMS_Assigned_Sites0) - In this example "ROLLUP" would work exactly the same The ‘simple’ grand total (CUBE or ROLLUP): SELECT sit.SMS_Assigned_Sites0 AS INNER JOIN dbo.v_RA_System_SMSAssignedSites sit Let’s create a simple ‘sub-total’ query: SELECT sit.SMS_Assigned_Sites0 AS In this SQL Tip we’ll look at the CUBE & ROLLUP functions. However, if you’re just writing a T-SQL query to find this data you’ll be interested in some ‘GROUP BY’ functions. If your query is being used in a reporting services report then this is easily achieved within the report. Sometimes when you write a query to find some totals by a particular category you’d also like to see the grand total. I have left this as originally written but have updated formatting for my WordPress theme.
Subtotal versus total versus grandtotal series#
This was originally posted to my internal (to Microsoft) blog on as part of a series I called ‘SQL Tips’ for coworkers and an email distribution list people could sign up to.