我試圖將 numHospitalizations 除以 numCases 以獲得住院百分比，并將 numDeaths 除以 numCases 以獲得死亡百分比。有很多重復的健康區，所以我認為最好按健康區分組，但我不斷得到：
不是 GROUP BY 運算式。
SELECT healthDistrict AS HEALTH_DISTRICT
, (numHospitalizations / numCases) AS PERCENTHOSP
, (numDeaths / numCases) AS PERCENTDEATH
FROM CovidReport
GROUP BY UPPER(healthDistrict);
uj5u.com熱心網友回復：
我猜這就是您所追求的，但是MCVE 會非常有用。
;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;
- 示例資料庫<>小提琴
uj5u.com熱心網友回復：
您需要形成所有未分組的列的聚合（并防止被零除）。
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);
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/333473.html
標籤：sql sql-server 查询语句 通过...分组
下一篇：SQL將一組按比例分配給其他組