Dividing values in a GROUP BY query-CodePudding

I am trying to divide numHospitalizations by numCases to get percent hospitalized and numDeaths by numCases to get percent death. There is a lot of repeat health districts so I thought it would be best to group by healthDistrict but I keep getting:

not a GROUP BY expression.

SELECT healthDistrict AS HEALTH_DISTRICT
    , (numHospitalizations / numCases) AS PERCENTHOSP
    , (numDeaths / numCases) AS PERCENTDEATH
FROM CovidReport
GROUP BY UPPER(healthDistrict);

CodePudding user response：

I'm guessing this is what you're after, but an MCVE would be really useful.

;WITH agg AS
(
  SELECT 
    UPPER(healthDistrict) AS HEALTH_DISTRICT, 
    Hosp   = SUM(numHospitalizations)*1.0,
    Cases  = NULLIF(SUM(numCases)*1.0,0),
    Deaths = SUM(numDeaths)*1.0
  FROM dbo.SomethingSlightlyLessMorbid
  GROUP BY UPPER(healthDistrict)
)
SELECT HEALTH_DISTRICT,
       PERCENTHOSP  = CONVERT(decimal(8,2), (Hosp   / Cases)), 
       PERCENTDEATH = CONVERT(decimal(8,2), (Deaths / Cases))
FROM agg;

Example db<>fiddle

CodePudding user response：

You need to form aggregates of all columns that you are not grouping on (and prevent division by zero).

SELECT
  UPPER(healthDistrict) AS HEALTH_DISTRICT,
  CASE WHEN SUM(numCases) > 0 THEN SUM(numHospitalizations) * 1.0 / SUM(numCases) ELSE 0 END AS PERCENTHOSP,
  CASE WHEN SUM(numCases) > 0 THEN SUM(numDeaths) * 1.0 / SUM(numCases) ELSE 0 END AS PERCENTDEATH
FROM
  SomethingSlightlyLessMorbid
GROUP BY
  UPPER(healthDistrict);