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. |