CIGNA payment vs zip

--CIGNA SUM CHARGE vs ZIP

SELECT SUM(convert(float, [CHRG_AMT])) Payment, [prov_zip] ZIP

--,NTILE(10) OVER (ORDER BY SUM(convert(float, [CHRG_AMT]))) AS Decile

  FROM [SiemensHealthPlanData].[dbo].[cignaSiemensFinalCensusWithPatientId] 

  group by [prov_zip]

  --order by Payment DESC 

  order by [prov_zip]

  

--CIGNA AVG CHARGE (per claim) vs ZIP

SELECT AVG(convert(float, [CHRG_AMT])) Payment, [prov_zip] ZIP

--,NTILE(10) OVER (ORDER BY AVG(convert(float, [CHRG_AMT]))) AS Decile

  FROM [SiemensHealthPlanData].[dbo].[cignaSiemensFinalCensusWithPatientId] 

  group by [prov_zip]

  --order by Payment DESC 

  order by [prov_zip]

  

  

--CIGNA AVG CHARGE (per subscriber) vs ZIP

SELECT

AVG(Q1.Payment) AvgPayment, Q1.ZIP

FROM (

    SELECT  SUM(convert(float, [CHRG_AMT])) Payment, [prov_zip] ZIP, [patient_id]

    FROM    [SiemensHealthPlanData].[dbo].[cignaSiemensFinalCensusWithPatientId] 

    GROUP BY [patient_id], [prov_zip]

) AS Q1

GROUP BY Q1.ZIP

order by Q1.ZIP