Grandtotal word5/28/2023 ![]() ![]() 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. 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. ![]()
0 Comments
Leave a Reply. |