By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.SomethingSlightlyLessMorbid
(
healthDistrict varchar(32),
numCases int,
numHospitalizations int,
numDeaths int
);
INSERT dbo.SomethingSlightlyLessMorbid VALUES
('mine',200,127,12),('yours',300,170,46),('miracle, TX',0,0,0);
3 rows affected
;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;
| HEALTH_DISTRICT | PERCENTHOSP | PERCENTDEATH |
|---|---|---|
| MINE | 0.64 | 0.06 |
| MIRACLE, TX | null | null |
| YOURS | 0.57 | 0.15 |