postgresql 选择查询自定义计算列以四舍五入到小数点后两位
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21580398/
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
Select query custom calculated column to round to 2 decimal places
提问by rohitmb
I have a table with some columns a,b, i need to add a custom column c_avg which will have the value c_avg = a*100/b upto 2 decimal values
我有一个包含一些列 a、b 的表，我需要添加一个自定义列 c_avg，它的值 c_avg = a*100/b 最多 2 个十进制值
my original table is some thing like this
我原来的桌子是这样的
id a b
1 1 2
2 2 3
3 2 0
I have come up with this query but seems as this returns me value as integer.
我想出了这个查询，但似乎这会以整数形式返回我的值。
select round( CAST((CASE WHEN b=0 THEN '0.00'
ELSE round(((a*100)/b),2)
END ) as numeric) , 2) as c_avg
from table_name
i get output to this as
我得到这个输出
a b c_avg
1 2 0
2 3 0
i need some thing like this
我需要这样的东西
a b c_avg
1 2 0.50
2 3 0.66
2 0 0
My Postgresql version on amazon redshift is PostgreSQL 8.0.2
我在 amazon redshift 上的 Postgresql 版本是 PostgreSQL 8.0.2
There is also a few things I'm doing with this table
我也在用这张桌子做一些事情
select sum(a) as aa, sum(b) as bb, groub_by_column
round( CAST((CASE WHEN sum(b)=0 THEN '0.00'
ELSE round(((sum(a)*100)/sum(b)),2)
END ) as numeric) , 2) as c_avg
from table group by groub_by_column
This returns me value to 0 and not to 0.*
这会将我的值返回到 0 而不是 0.*
Thanks
谢谢
The division operation in postgresql truncates to integer value just found that
postgresql中的除法运算截断为整数值刚发现
select round((4000/576::float),3) as result;
adding meta ::floatto the division operation gives the desired result, it does not truncates the output to integer value. Thanks
将 meta ::float添加到除法运算会给出所需的结果，它不会将输出截断为整数值。谢谢
回答by rohitmb
The division operation in postgresql truncates to integer value just found that
postgresql中的除法运算截断为整数值刚发现
select round((4000/576::float),3) as result;
adding meta ::floatto the division operation gives the desired result, it does not truncates the output to integer value.
将 meta ::float添加到除法运算会给出所需的结果，它不会将输出截断为整数值。
Thanks
谢谢
回答by Tomasz Tybulewicz
While working on integers, the result is also an integer - multiply by a float value to get a float result:
在处理整数时，结果也是整数 - 乘以浮点值以获得浮点结果：
SELECT
sum(a) as aa,
sum(b) as bb,
group_by_column,
CASE
WHEN 0 = sum(b) THEN 0.0
ELSE ROUND(100.0 * sum(b) / sum(b), 2)
END AS c_avg
FROM table_name
GROUP BY group_by_column
回答by Gordon Linoff
Postgres does integer division, so try this version of your query:
Postgres 做整数除法，所以试试这个版本的查询：
select round((CASE WHEN b=0 THEN 0.00
ELSE a*100.0)/b
END), 2) as c_avg
from table_name;
You can also do this by converting the result to a decimal:
您也可以通过将结果转换为小数来实现：
select cast((case when b = 1 then 0 else a*100.0/b end) as decimal(5, 2)) as c_avg
from table_name;
回答by atiruz
Try with this:
试试这个：
SELECT to_char (1/2::FLOAT, 'FM999999990.00');
--- RESULT: 0.50
Or simply:
或者干脆：
SELECT round (2/3::DECIMAL, 2)::TEXT
--- RESULT: 0.67
回答by d345k0
you need cast
你需要演员
select round( (2/3), 2) produces 0
select round( (2/3::numeric), 2) produces 0.67
