GroupSetName    GroupTotalName      StaffDescription                Visits
--------------------------------------------------------------------------
DrGroup         NULL                Internists                      1
DrGroup         NULL                Family Physicians               3622
DrGroup         NULL                Other Specialty Physicians      318
GenMedGroup     DrGroupTotal        Total Physicians                3941
MedAsstGroup    NULL                Nurse Practitioners             2221
GenMedGroup     MedAsstGroupTotal   Total NPs, PAs, and CNMs        2221
GenMedGroup     NULL                Nurses                          6
GenMedGroup     NULL                Other Medical Personnel         NULL
GrandTotalGroup GenMedGroupTotal    Total Medical                   6

;WITH CTE
AS
(
SELECT SG.GroupSetName,
      CASE WHEN SG.UDSStaffGroupDescription IS NULL
                THEN (SELECT UDSStaffGroupDescription FROM UDSStaffUtilizationGroups WHERE GroupTotalName = SG.GroupSetName + 'Total')
            ELSE SG.UDSStaffGroupDescription END AS UDSStaffGroupDescription,
    CASE WHEN COUNT(E.PatientEncounterID) = 0 THEN 0 ELSE COUNT(E.PatientEncounterID) END AS Visits
FROM UDSStaffUtilizationGroups SG
        LEFT OUTER JOIN UDSStaffUtilizationMapping SM ON SM.UDSStaffGroupID = SG.UDSStaffGroupID
        LEFT OUTER JOIN Provider P ON P.Specialty = SM.SpecialtyName
        LEFT OUTER JOIN Encounter E ON E.ProviderID = P.ProviderID
        LEFT OUTER JOIN PatientInfo PatInfo ON PatInfo.PatientProfileID = E.PatientProfileID
WHERE E.ResourceType LIKE 'Doctor%' AND E.EncounterCounted = 1
GROUP BY GROUPING SETS((SG.GroupSetName, SG.UDSStaffGroupDescription), (SG.GroupSetName))
UNION
SELECT SG.GroupSetName,
      CASE WHEN SG.UDSStaffGroupDescription IS NULL
                THEN (SELECT UDSStaffGroupDescription FROM UDSStaffUtilizationGroups WHERE GroupTotalName = SG.GroupSetName + 'Total')
            ELSE SG.UDSStaffGroupDescription END AS UDSStaffGroupDescription,
    CASE WHEN COUNT(E.PatientEncounterID) = 0 THEN 0 ELSE COUNT(E.PatientEncounterID) END AS Visits
FROM UDSStaffUtilizationGroups SG
        LEFT OUTER JOIN UDSStaffUtilizationMapping SM ON SM.UDSStaffGroupID = SG.UDSStaffGroupID
        LEFT OUTER JOIN Encounter E ON E.ResourceType = SM.SpecialtyName
        LEFT OUTER JOIN PatientInfo PatInfo ON PatInfo.PatientProfileID = E.PatientProfileID
WHERE E.EncounterCounted = 1
GROUP BY GROUPING SETS((SG.GroupSetName, SG.UDSStaffGroupDescription), (SG.GroupSetName))
)
SELECT DISTINCT SG1.LineOrder, SG1.GroupSetName, SG1.GroupTotalName, SG1.UDSStaffGroupDescription, Visits--, Patients
FROM UDSStaffUtilizationGroups SG1 LEFT OUTER JOIN CTE  ON CTE.UDSStaffGroupDescription = SG1.UDSStaffGroupDescription

GroupSetName    GroupTotalName      StaffDescription                Visits
    --------------------------------------------------------------------------
    DrGroup         NULL                Internists                      1
    DrGroup         NULL                Family Physicians               3622
    DrGroup         NULL                Other Specialty Physicians      318
    GenMedGroup     DrGroupTotal        Total Physicians                3941
    MedAsstGroup    NULL                Nurse Practitioners             2221
    GenMedGroup     MedAsstGroupTotal   Total NPs, PAs, and CNMs        2221
    GenMedGroup     NULL                Nurses                          6
    GenMedGroup     NULL                Other Medical Personnel         NULL
    GrandTotalGroup GenMedGroupTotal    Total Medical                   6168