SQL 查询：计算百分比
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9572139/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Query: calculating percentage
提问by Holly
If I wanted to find the percentage of people that are from the zip code area of 12345, I would take the number of people with that zip code and divide it from the total number of people… what am I missing in the example query below? I can't figure out how to display the percentage correctly. It just keeps saying 0%.
如果我想找到来自 12345 邮政编码区域的人数百分比，我会取具有该邮政编码的人数并将其与总人数相除……我在下面的示例查询中遗漏了什么？我不知道如何正确显示百分比。它只是一直说0％。
select (count(P.PERSON_ID) / (select count(*) from PERSON P)) * 100.00
as “12345 Zip Code Percentage”
from PERSON P
where P.ZIP = '12345'
Thank you.
谢谢你。
回答by John Pick
You're dividing an integer by a larger integer. The result is always going to be zero. Instead, multiply the first count by 100.0 beforedivision. That converts the first count to a floating point number which, when divided by an integer, will give a floating point number and thus the percentage you need.
您将一个整数除以一个更大的整数。结果总是为零。相反，在除法之前将第一个计数乘以 100.0 。这会将第一个计数转换为浮点数，当除以整数时，将给出一个浮点数，从而给出您需要的百分比。
回答by Msonic
You're doing an integer division. If the result is lower to 1, it will always display 0 * 100.00, so always 0. If you want an accurate percentage, you need to cast one of the variables as a float.
你在做一个整数除法。如果结果低于 1，它将始终显示 0 * 100.00，因此始终为 0。如果您想要一个准确的百分比，则需要将其中一个变量转换为浮点数。
select (count(P.PERSON_ID) / CAST ((select count(*) from PERSON P) As Float)) * 100.00
as “12345 Zip Code Percentage”
from PERSON P
where P.ZIP = '12345'
回答by CMorgan
If you want to choose the number of decimal points you could also incorporate ROUND()as follows:
如果您想选择小数点的数量，您还可以ROUND()按如下方式合并：
,ROUND(CAST(COUNT(var1) AS FLOAT)/COUNT(var2),[# decimals]) AS 'mypercentage'
回答by Jumsheed
select AVG(CASE WHEN ZIP = '12345'
THEN 1 ELSE 0
END) * 100.00 as “12345 Zip Code Percentage”
from PERSON P
回答by rs.
Try this, i added extra condition to avoid divide by 0 error:
试试这个，我添加了额外的条件以避免除以 0 错误：
select
CASE
WHEN (select count(*) from PERSON P) > 0 THEN (CAST(count(P.PERSON_ID) As Decimal) / CAST((select count(*) from PERSON P)As Decimal)) * 100.00
ELSE 0 END
as “12345 Zip Code Percentage”
from PERSON P
where P.ZIP = '12345'
回答by Chandu
Try this:
尝试这个：
SELECT (P.SubSet * 100.00)/p.Total AS "12345 Zip Code Percentage"
FROM (
SELECT COUNT(CASE WHEN ZIP = '12345' THEN 1 ELSE 0 END ) Subset,
COUNT(*) Total
FROM PERSON
) P
