我有一個查詢，它提取按年份和標識號中字符長度分組的記錄總數。我想添加一個列來計算這個總數占每個記錄年總數的百分比。
這是我正在使用的查詢，到目前為止，我能夠提取年份、ID 長度和記錄數；但是，我無法得出按年份分組的總數百分比：
/* Assess length of McrCertID_112 field for 2020. Count the number and percent of records: */
SELECT
DemoRateYear_101,
length(McrCertId_112),
count(*) AS 'Num_of_Records',
concat((count(*) / (SELECT count(*) FROM upl_db_prod.tblProviderDetails) * 100), '%') AS 'Percentage'
FROM upl_db_prod.tblProviderDetails
GROUP BY length(McrCertId_112), DemoRateYear_101
ORDER BY DemoRateYear_101, length(McrCertId_112);
這就是我希望決賽桌的樣子：
DemoRateYear_101 length(McrCertId_112) Num_of_Records Percentage
2017 4 10 47.6190%
2017 5 11 52.3809%
2018 4 8 26.6667%
2018 5 10 33.3333%
2018 7 12 40.0000%
有沒有一種方法可以在一個查詢中完成此操作？感謝您的關注！
uj5u.com熱心網友回復：
如果視窗函式可用，您可以這樣做：
SELECT
demorateyear_101,
LENGTH(mcrcertid_112),
COUNT(*) AS num_of_records,
COUNT(*) / SUM(COUNT(*) OVER (PARTITION BY demorateyear_101)) * 100 AS percentage
FROM tblproviderdetails
GROUP BY demorateyear_101, LENGTH(mcrcertid_112)
ORDER BY demorateyear_101, LENGTH(mcrcertid_112);
否則需要子查詢（where 子句，如果有的話，必須匹配）：
SELECT
demorateyear_101,
LENGTH(mcrcertid_112),
COUNT(*) AS num_of_records,
COUNT(*) / (
SELECT COUNT(*)
FROM tblproviderdetails AS x
WHERE x.demorateyear_101 = tblproviderdetails.demorateyear_101
) * 100 AS percentage
FROM tblproviderdetails
GROUP BY demorateyear_101, LENGTH(mcrcertid_112)
ORDER BY demorateyear_101, LENGTH(mcrcertid_112);
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/417211.html
標籤：
下一篇：在SQL中插入多序列資料