I have a table with a smallint column that contains percentages as whole numbers (i.e., 50, 75, 85, etc.)
我有一个带有smallint列的表，其中包含百分比作为整数（即50,75,85等）
When I divide this column by 100, as in
当我将此列除以100时，如
SELECT MY_COLUMN/100 AS PCT_AS_FRACTION
FROM MY_TABLE
the result is rounded to the nearest whole number. For example, for a row that contains the number "50", I get zero as my result.
结果四舍五入到最接近的整数。例如，对于包含数字“50”的行，我的结果为零。
I can duplicate this with a simple statement:
我可以用一个简单的声明复制它：
SELECT 50 / 100 AS TEST_VALUE
Why is that, and how can I get more precision in my result?
为什么会这样，我怎样才能在结果中获得更高的精确度？
6 个解决方案
#1
32
When you do integer division (integer divided by integer) you always get an integer answer. 50/100 = .50, which is 0 in integer-speak.
当你进行整数除法（整数除以整数）时，你总是得到一个整数答案。 50/100 = .50，整数形式为0。
Have you tried dividing MY_COLUMN by 100.0?
你试过将MY_COLUMN除以100.0吗？
#2
24
Cast whole numbers.
投整整数。
SELECT (cast(50 AS float)/100)
#3
1
You're doing integer division. 50/100 is 0 with a remainder of 50.
你正在进行整数除法。 50/100为0，余数为50。
You need to use floating point division.
你需要使用浮点除法。
#4
1
NB - Be careful in that the remainder of integer division is not rounded. Rather, it is dropped. This is equivalent to calling the FLOOR sql function.
注意 - 请注意整数除法的余数不是四舍五入的。相反，它被删除了。这相当于调用FLOOR sql函数。
This is common, and is defined as such because when multiplying two integers, a fraction will never occur. Therefore a fraction-handling methodology is never assumed when multiplying integers, but the same cannot be said for integer division.
这是常见的，并且被定义为这样，因为当乘以两个整数时，将永远不会出现分数。因此，在乘以整数时从不假设分数处理方法，但对于整数除法则不能说同样的方法。
This can often have an impact when doing dateTime arithmetic in SQL.
在SQL中执行dateTime算法时，这通常会产生影响。
#5
1
When you are using /(Divide) operator it
当你使用/（Divide）运算符时
Returns the data type of the argument with the higher precedence.
返回具有更高优先级的参数的数据类型。
and
和
If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.
如果整数被除数除以整数除数，则结果是一个整数，其结果的任何小数部分都被截断。
So, you need to cast at least one of the operands to appropriate type: decimal and numeric or float or real.
因此，您需要将至少一个操作数强制转换为适当的类型：decimal和numeric或float或real。
#6
0
You're dividing 2 integers which results in another integer.
你正在划分2个整数，这导致另一个整数。
It should possible to cast that way, too
也应该这样投射
SELECT (50/100)::numeric;
SELECT（50/100）::数字;
#1
32
When you do integer division (integer divided by integer) you always get an integer answer. 50/100 = .50, which is 0 in integer-speak.
当你进行整数除法（整数除以整数）时，你总是得到一个整数答案。 50/100 = .50，整数形式为0。
Have you tried dividing MY_COLUMN by 100.0?
你试过将MY_COLUMN除以100.0吗？
#2
24
Cast whole numbers.
投整整数。
SELECT (cast(50 AS float)/100)
#3
1
You're doing integer division. 50/100 is 0 with a remainder of 50.
你正在进行整数除法。 50/100为0，余数为50。
You need to use floating point division.
你需要使用浮点除法。
#4
1
NB - Be careful in that the remainder of integer division is not rounded. Rather, it is dropped. This is equivalent to calling the FLOOR sql function.
注意 - 请注意整数除法的余数不是四舍五入的。相反，它被删除了。这相当于调用FLOOR sql函数。
This is common, and is defined as such because when multiplying two integers, a fraction will never occur. Therefore a fraction-handling methodology is never assumed when multiplying integers, but the same cannot be said for integer division.
这是常见的，并且被定义为这样，因为当乘以两个整数时，将永远不会出现分数。因此，在乘以整数时从不假设分数处理方法，但对于整数除法则不能说同样的方法。
This can often have an impact when doing dateTime arithmetic in SQL.
在SQL中执行dateTime算法时，这通常会产生影响。
#5
1
When you are using /(Divide) operator it
当你使用/（Divide）运算符时
Returns the data type of the argument with the higher precedence.
返回具有更高优先级的参数的数据类型。
and
和
If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.
如果整数被除数除以整数除数，则结果是一个整数，其结果的任何小数部分都被截断。
So, you need to cast at least one of the operands to appropriate type: decimal and numeric or float or real.
因此，您需要将至少一个操作数强制转换为适当的类型：decimal和numeric或float或real。
#6
0
You're dividing 2 integers which results in another integer.
你正在划分2个整数，这导致另一个整数。
It should possible to cast that way, too
也应该这样投射
SELECT (50/100)::numeric;
SELECT（50/100）::数字;