oracle 如何从聚合 SUM() 的结果中设置位数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5521318/
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 set the number of digits from results of aggregate SUM()?
提问by ialm
In Oracle, I am trying to obtain the sum of sales, which are of the type DECIMAL(5,2). I also want the SUM'd column to return with 2 decimal places.
在 Oracle 中，我试图获取 DECIMAL(5,2) 类型的销售额总和。我还希望 SUM 列返回小数点后两位。
The query looks like this:
查询如下所示：
SELECT I.upc, category, sellPrice, total, sum(TOTAL*sellPrice)"TOTALVALUE"
...
GROUP BY GROUPING SETS ( (I.upc, category, sellPrice, TOTAL), (category), ());
An example of the table I receive from the query is:
我从查询中收到的表的一个例子是：
UPC CATEGORY SELLPRICE TOTAL TOTALVALUE
--- ---------- --------- ----- ----------
0 Fantasy 20.99 2 41.98
1 Fantasy 30.99 5 154.95
Fantasy 196.93
2 Classical 10.99 3 32.97
Classical 32.97
229.9
Note that the last value at the bottom right, the grand total, is set with 1 decimal place.
请注意，右下角的最后一个值（总计）设置为小数点后 1 位。
I have tried to cast the sum as a DECIMAL(5,2) type, but to no avail. Any and all help would be appreciated.
我试图将总和转换为 DECIMAL(5,2) 类型，但无济于事。任何和所有帮助将不胜感激。
采纳答案by Martin Schapendonk
to_char(sum(TOTAL*sellPrice), 'FM99999990D00')
The FM removes leading blanks. The 9's represent an optional number in that position. The D represents the decimal separator. The 0's represent mandatory numbers in that position.
FM 删除前导空格。9 代表该位置的可选数字。D 代表小数点分隔符。0 代表该位置的强制性数字。
This ensures that all number have at least two decimals.
这确保所有数字至少有两位小数。
回答by Benoit
Use TO_CHARwith format specifier99999D99for example?
例如TO_CHAR与格式说明符99999D99一起使用？
Your problem here is that the last digit would be a zero.
你的问题是最后一位数字是零。
回答by Daniel Williams
Well, this seems to just be a matter of display. If you case to 5,2, it will be 5,2. What matters is how you display it later on. Just because your tool is not showing the last zero, does not mean it is not there.
好吧，这似乎只是一个展示问题。如果你是 5,2，那将是 5,2。重要的是您以后如何显示它。仅仅因为您的工具没有显示最后一个零，并不意味着它不存在。
