我有一個特定日期從 2020 年到 2022 年特定日期的資料 covid 死亡記錄。我想計算每個國家使用 group by 的國家 2 年的死亡率。
我嘗試這樣進行查詢，但查詢不起作用，因為不允許使用子查詢作為運算式。如何使這個查詢作業？謝謝你。
SELECT
location,
sum(total_deaths) as total_deaths,
sum(total_cases) as total_cases,
(select SUM(total_deaths)
FROM CovidDeaths$
GROUP BY location
)/
(select sum(total_cases)
FROM CovidDeaths$
GROUP BY location
) *100 as DeathPercentage
FROM CovidDeaths$
WHERE NOT (total_deaths is null
OR total_cases is null
OR continent is null
)
GROUP BY location
ORDER BY 1
*查詢中的位置是指國家
我嘗試進行嵌套查詢，但死亡百分比的值變為 0。
SELECT location, total_deaths1, total_cases1, total_deaths1/total_cases1*100 as Death_Percentage
FROM(
SELECT location, sum(total_deaths) as total_deaths1, sum(total_cases) as total_cases1
FROM CovidDeaths$
WHERE NOT (total_deaths is null OR total_cases is null OR continent is null)
GROUP BY location
) as death
ORDER BY 1
uj5u.com熱心網友回復：
當整數用于除法時，許多資料庫使用整數算術。0由于和之間沒有整數1，這意味著您的所有值都被四舍五入到0乘以 之前100。
2 / 6 * 100=>0 * 100=>0
您可以簡單地更改操作的順序
100 * 2 / 6=>200 / 6=>66
或者，將值隱式轉換為非整數型別
100.0 * 2 / 6=>200.0 / 6=>33.3333333
您還可以使用CAST()、CONVERT()或其他函式來顯式轉換資料型別（可能為DECIMAL(18,6)或FLOAT，取決于您的需要）。
- 確切的語法取決于您使用的資料庫
- 但是您還沒有指定哪個資料庫
uj5u.com熱心網友回復：
您不需要在子查詢中計算平均值，只需在主查詢中計算即可
(SUM(total_deaths)/sum(total_cases))*100
更新
此計算列是您按位置分組的主要查詢的一部分：
SELECT
location,
sum(total_deaths) as total_deaths,
sum(total_cases) as total_cases,
(SUM(total_deaths)/sum(total_cases))*100
FROM CovidDeaths$
WHERE NOT (total_deaths is null
OR total_cases is null
OR continent is null
)
GROUP BY location
ORDER BY 1
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/463079.html