如何在 SQL 中用小数计算百分比？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/400557/
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
How do I calculate percentages with decimals in SQL?
提问by LeppyR64
How can i convert this to a decimal in SQL? Below is the equation and i get the answer as 78 (integer) but the real answer is 78.6 (with a decimal) so i need to show this as otherwise the report won't tally up to 100%
如何在 SQL 中将其转换为小数？下面是等式，我得到的答案是 78（整数），但真正的答案是 78.6（带小数）所以我需要显示这个，否则报告不会达到 100%
(100 * [TotalVisit1]/[TotalVisits]) AS Visit1percent
采纳答案by Shawn
convert(decimal(5,2),(100 * convert(float,[TotalVisit1])/convert(float,[TotalVisits]))) AS Visit1percent
Ugly, but it works.
丑陋，但它的工作原理。
回答by LeppyR64
Try This:
尝试这个：
(100.0 * [TotalVisit1]/[TotalVisits]) AS Visit1percent
回答by Charles Bretana
Just add a decimal to the 100
只需在 100 上加一个小数
(100.0 * [TotalVisit1]/[TotalVisits]) AS Visit1percent
this forces all processing to happen in floats... if you want the final output as text, and truncated for display to only one decimal place, use Str function
这会强制所有处理以浮点数形式发生...如果您希望最终输出为文本，并截断以仅显示一位小数，请使用 Str 函数
Str( 100.0 * [TotalVisit1]/[TotalVisits], 4, 1 ) AS Visit1percent
回答by ólafur Waage
At least in MySQL (if it helps), if you want to use float numbers you had to use a type float field, not the regular int fields.
至少在 MySQL 中（如果有帮助的话），如果你想使用浮点数，你必须使用浮点型字段，而不是常规的 int 字段。
回答by Ali
CAST(ROUND([TotalVisit1]*100.0/[TotalVisits],2)as decimal(5,2)) as Percentage
Not ugly and work better and fast , enjoy it!
不丑，工作更好更快，享受它！
回答by Goku
This works perfectly for me:
这对我来说非常有效：
CAST((1.0 * SUM(ColumnA) /COUNT(ColumnB)) as decimal(5,2))
Hope it helps someone out there in the world.
希望它可以帮助世界上的人。
回答by Zameer Fouzan
Try with this, no round and str or Over(). i found this as a simpler way to do it.
试试这个，没有 round 和 str 或 Over()。我发现这是一种更简单的方法。
cast((count(TotalVisit1) * 100.0) /TotalVisits as numeric(5,3)) as [Visit1percent]
You can change the number of decimal points as you wish to
您可以根据需要更改小数点的数量
e.g. numeric(5,2) or numeric(5,4)
例如数字（5,2）或数字（5,4）
回答by Kent Fredric
Its probably overkill to do this, but you may wish to consider casting all values to floats to ensure accuracy at all phases.
这样做可能有点矫枉过正，但您可能希望考虑将所有值转换为浮点数以确保所有阶段的准确性。
(100.0 * ( [TotalVisit1]+0.0 )/([TotalVisits]+0.0) ) as Visit1percent
Note, you really need to put code in here for the case that TotalVisits == 0 or you will get a division by 0 answer.
请注意，对于 TotalVisits == 0 的情况，您确实需要在此处放置代码，否则您将得到除以 0 的答案。
回答by JoBaxter
SELECT(ROUND(CAST(TotalVisit1 AS DECIMAL)/TotalVisits,1)) AS 'Visit1percent'
This will return a decimal and the ROUND will round it to one digit. So in your case you would get 76.6. If you don't want any digits change the 1 to 0 and if you want two digits change it to 2.
这将返回一个小数，而 ROUND 会将其四舍五入为一位。所以在你的情况下，你会得到 76.6。如果您不希望任何数字将 1 更改为 0，如果您想要两位数将其更改为 2。
回答by larssbr
In ms Access You can use the SQL function ROUND(number, number of decimals), It will round the number to the given number of decimals:
在 ms Access 中，您可以使用 SQL 函数 ROUND(number, number of decimals)，它将数字四舍五入到给定的小数位数：
ROUND((100 * [TotalVisit1]/[TotalVisits]),1) AS Visit1percent
ROUND((100 * [TotalVisit1]/[TotalVisits]),1) AS 访问 1%
