How to get sum of results from GROUPING SETS RRS feed

  • Question

  • Hello,

    I have a previous post that became more complicated.  See the post here:

    Previous issue

    I needed to add in the Fiscal Year (FY) to group my results by FY.  I changed my code to the following:

    EXEC ADAMHsp_BSS_GetNonMedicaidReportTotals
    		@pstrProviderName = 'FAM SER-WOOD'   
    ALTER PROCEDURE [dbo].[ADAMHsp_BSS_GetNonMedicaidReportTotals]
    	@pstrProviderName			VARCHAR(100)
    	era_provider_name AS Provider,
    	RIGHT([era_upi], 5) AS 'ERA Upi',
    	[era_fy] AS 'ERA FY',
    	COUNT(DISTINCT UCI) AS 'Client Count',
    	SUM(era_billed_amount) AS 'Billed Amt',
    	SUM(era_allowed_amount) AS 'Allowed Amt',
    	SUM(era_copayment_amount) AS 'Copay Amt',
    	SUM(era_deductible_amount) AS 'Deduct. Amt',
    	SUM(era_other_carrier_amount) AS 'Other Carrier',
    	SUM(era_withhold_amount) AS 'WH Amt',
    	SUM(era_not_covered_amount) AS 'Not Cvr''d Amt',
    	SUM(PayAdjust) AS 'Payment Adjust',
    	SUM(AdjAmt) AS '*Adj. Amt',
    	SUM(era_non_net_total) AS '**Net Amt',
    	SUM(era_units) AS Units,
    	[account_code] AS 'Account Code'
    	FROM dbo.t_bss_non_mcd_report
    WHERE era_provider_name = @pstrProviderName 
    GROUP BY GROUPING SETS (( era_provider_name, [era_upi], [era_fy], ProcGrp, [account_code]),(era_provider_name, [era_fy]) )
    ORDER BY era_provider_name

    See my result set in the picture below.  The rows with NULL in the 'ProcGrp' column have the totals of the groupings by FY that I am looking for - that's great.  What I want to do now is have another row that contains the sums of the values from any row where 'ProcGrp' is null so that I have a totals row.

    • Edited by Xanalyth Wednesday, July 8, 2015 7:26 PM
    Wednesday, July 8, 2015 7:25 PM


All replies

  • Check this blog post by Jacob Sebastian

    T-SQL Tuesday #016 - Summarizing data using GROUPING SETS()

    I personally open it every time when I need something involving Grouping Sets. It shows you how to add Total Row.

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    My TechNet articles

    • Marked as answer by Xanalyth Wednesday, July 8, 2015 8:03 PM
    Wednesday, July 8, 2015 7:33 PM
  • Naomi,

    Thank you for that prompt reply.  I was able to use the example using the GRAND TOTAL and it worked perfectly.  Thank you much!

    Wednesday, July 8, 2015 8:03 PM
  • GROUP BY GROUPING SETS (( era_provider_name,era_fy, ProcGrp, [account_code]),(era_provider_name,era_fy),( era_provider_name) )
    Order by era_provider_name

    Wednesday, July 8, 2015 8:08 PM