• If you have a query already that returns the region and product line, the you could try the query below using sql servers OVER clause to partition the sums by region. Just replace my values table with your actual table joins.
    Also, this way the order of case statements doesn't matter, b/c the -1 for 'M%POS%' cancels out the 1 for '%POS%'.


    select distinct
        x.region
        ,sum(case when x.productline like '%HMO%' then 1 else 0 end
            + case when x.productline like '%POS%' then 1 else 0 end
            + case when x.productline like '%PPO%' then 1 else 0 end
            + case when x.productline like '%EPO%' then 1 else 0 end
            + case when x.productline like 'M%POS%' then -1 else 0 end) over (partition by x.region) Commercial_Total
        ,sum(case when x.productline like '%Medicare%' then 1 else 0 end) over (partition by x.region) Medicare_Total
        ,sum(case when x.productline like '%Medicaid%' then 1 else 0 end) over (partition by x.region) Medicaid_Total
    from ( values
            ('1','Medicare')
            ,('1','PPO')
            ,('1','Medicaid')
            ,('4','HMO')
            ,('1','PPO')
            ,('2','Medicaid')
            ,('3','Medicare')
            ,('2','HMO')
            ,('2','EPO')
            ,('1','Medicare')
            ,('2','POS')
            ,('2','Medicaid')
            ,('3','HMO')
            ,('2','HMO')
            ,('3','Medicaid')
            ,('1','EPO')
            ,('3','POS')
            ,('3','M1POS')
            ,('3','Medicaid')
            ,('1','Medicaid Dual Eligible')
            ,('4','Medicare')
            ,('1','POS')
            ,('4','Medicare')
            ,('1','Medicaid')
            ,('4','EPO')
            ,('5','Medicaid')
            ,('5','Medicaid Dual Eligible')
            ,('5','Medicaid')
    ) x(region,productline)

    that would return results like this: