CODEWITHSUNDEEP
sql-serverhaving-clause
Gloria Santin
Gloria Santin

Reputation: 2136

Sum multiple groups in the having clause

I have the following query that display counts by insurance type. This is the query:

;WITH CTE
AS
(
SELECT DISTINCT ROW_NUMBER() OVER (Partition by PatInfo.PatientProfileID Order By E.Visit desc) AS RowNumber, 
        PatInfo.PatientProfileID, E.Visit, PatInfo.Zip, E.CarrierTypeDesc, E.FinancialClassDescription, IG.UDSInsuranceGroupName,IG.UDSInsuranceID
FROM Encounter E JOIN CHCEncounterType ET ON E.CHCEncounterTypeID = ET.CHCEncounterTypeID       
        JOIN PatientInfo PatInfo ON PatInfo.PatientProfileID = E.PatientProfileID
        LEFT JOIN dbo.UDSInsuranceMapping ON ISNULL(dbo.UDSInsuranceMapping.InsuranceName,'') = ISNULL(E.FinancialClassDescription,'') 
        LEFT JOIN dbo.UDSInsuranceGroups IG ON IG.UDSInsuranceID = dbo.UDSInsuranceMapping.UDSInsuranceID            
WHERE E.EncounterCounted = 1 
) 

SELECT Zip AS ZipCode, 
    Count(PatientProfileID) as Total, UDSInsuranceGroupName, UDSInsuranceID
from CTE 
where RowNumber = 1 AND ZIP IS NOT NULL 
group by Zip, UDSInsuranceGroupName, UDSInsuranceID

An example of the zip code data returned is as follows:

Zip Code  Total InsuranceGroup             InsuranceID 
19522       9   Medicaid/CHIP/Other/Public      2 
19522       1   Medicare                        3 
19522       1   None/Uninsured                  1 
19522       1   Private                         4
19512       2   Medicaid/CHIP/Other/Public      2
19512       1   None/Uninsured                  1
19518       1   Medicaid/CHIP/Other/Public      2
19518       1   Medicare                        3

I would like to limit display to only the zip codes that total more than 10. So the zip code 19522 would be displayed because there are 12 but the other would not be displayed. If I add a having clause the 19522 zip is not displayed because there are only 9 in one of the insurance categories. I need to sum all of the insurance categories per zip code.

How can I do that?

Upvotes: 1

Views: 55

Answers (2)

Nolan Shang
Nolan Shang

Reputation: 2328

    ;WITH CTE
    AS
    (
    SELECT DISTINCT ROW_NUMBER() OVER (Partition by PatInfo.PatientProfileID Order By E.Visit desc) AS RowNumber, 
            COUNT(0)OVER(PARTITION BY PatInfo.PatientProfileID) AS ZipTotalCount, 
            PatInfo.PatientProfileID, E.Visit, PatInfo.Zip, E.CarrierTypeDesc, E.FinancialClassDescription, IG.UDSInsuranceGroupName,IG.UDSInsuranceID
    FROM Encounter E JOIN CHCEncounterType ET ON E.CHCEncounterTypeID = ET.CHCEncounterTypeID       
            JOIN PatientInfo PatInfo ON PatInfo.PatientProfileID = E.PatientProfileID
            LEFT JOIN dbo.UDSInsuranceMapping ON ISNULL(dbo.UDSInsuranceMapping.InsuranceName,'') = ISNULL(E.FinancialClassDescription,'') 
            LEFT JOIN dbo.UDSInsuranceGroups IG ON IG.UDSInsuranceID = dbo.UDSInsuranceMapping.UDSInsuranceID            
    WHERE E.EncounterCounted = 1 
    ) 

    SELECT Zip AS ZipCode, ZipTotalCount,
        Count(PatientProfileID) as Total, UDSInsuranceGroupName, UDSInsuranceID
    from CTE 
    where RowNumber = 1 AND ZIP IS NOT NULL AND ZipTotalCount>10
    group by Zip, UDSInsuranceGroupName, UDSInsuranceID,ZipTotalCount

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You can use window functions, but this requires another level of subquery:

with . ..
select *
from (select Zip AS ZipCode, 
             Count(*) as Total, UDSInsuranceGroupName,
             UDSInsuranceID,
             sum(count(*)) over (partition by zip) as zipTotal
      from CTE 
      where RowNumber = 1 AND ZIP IS NOT NULL 
      group by Zip, UDSInsuranceGroupName, UDSInsuranceID 
     ) z
where zipTotal > 10;

Upvotes: 5

Related Questions