blocks|key|1681840|text|我重新格式化了您的示例代码，以尝试更容易地理解它：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1681841|select++round(
++++++++++++round(
++++++++++++++++sum(
++++++++++++++++++++case
++++++++++++++++++++++++when++++acct_no+=+'2999'
++++++++++++++++++++++++++++++++and+date+between+'1/1/14'+and+current_date+then+amount
++++++++++++++++++++end+)::numeric,
++++++++++++++++4+)::float
++++++++++++/+round(
++++++++++++++++sum(
++++++++++++++++++++case
++++++++++++++++++++++++when++++acct_no+=+'3989'
++++++++++++++++++++++++++++++++and+date+between+'1/1/14'+and+current_date+then+amount
++++++++++++++++++++end+)::numeric,
++++++++++++++++4+)::numeric,
++++++++++++4+)+column
from+table|code-block|syntax|javascript|1681842|问题是，您正在将除法操作的分子转换为float数据类型double+precision。|offset|length|style|CODE|1681843|round(
++++sum(
++++++++case
++++++++++++when++++acct_no+=+'2999'
++++++++++++++++++++and+date+between+'1/1/14'+and+current_date+then+amount
++++++++end+)::numeric,
++++4+)::float
/+round(
++++sum(
++++++++case
++++++++++++when++++acct_no+=+'3989'
++++++++++++++++++++and+date+between+'1/1/14'+and+current_date+then+amount
++++++++end+)::numeric,
++++4+)::numeric|1681844|因此，表达式的结果是一个double+precision值，而不是numeric值，因此出现了您观察到的错误。|1681845|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/9.1/interactive/datatype-numeric.html^0|0|0|I|5|R|G|R|G|0|0|0|C|G|X|7|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@$I|11|J|12|K|L]|$I|13|J|14|K|L]]|9|@$I|15|J|16|1|17]]|A|$]]|$1|M|3|N|5|D|7|18|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|19|8|@$I|1A|J|1B|K|L]|$I|1C|J|1D|K|L]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]]]

I re-formatted your example code to try and understand it more easily:

<pre><code>select round(
 round(
 sum(
 case
 when acct_no = '2999'
 and date between '1/1/14' and current_date then amount
 end )::numeric,
 4 )::float
 / round(
 sum(
 case
 when acct_no = '3989'
 and date between '1/1/14' and current_date then amount
 end )::numeric,
 4 )::numeric,
 4 ) column
from table
</code></pre>

The problem is that you're casting the numerator of your division operation as a <code>float</code> data type, <a href="http://www.postgresql.org/docs/9.1/interactive/datatype-numeric.html" rel="nofollow">which, because you didn't specify a precision, is equivalent to <code>double precision</code></a>.

<pre><code>round(
 sum(
 case
 when acct_no = '2999'
 and date between '1/1/14' and current_date then amount
 end )::numeric,
 4 )::float
/ round(
 sum(
 case
 when acct_no = '3989'
 and date between '1/1/14' and current_date then amount
 end )::numeric,
 4 )::numeric
</code></pre>

So the result of the expression is a <code>double precision</code> value instead of as a <code>numeric</code> value, hence the error you observed.

blocks|key|1681928|text|SELECT+round((
++++++++++sum(CASE+WHEN+acct_no+=+'2999'+
++++++++++++++AND+thedate+between+'2014-1-1'+AND+current_date+THEN+amount+END)
++++++++/+sum(CASE+WHEN+acct_no+=+'3989'+
++++++++++++++AND+thedate+between+'2014-1-1'+AND+current_date+THEN+amount+END)
+++++++)::numeric,+4)+AS+result
FROM+++tbl;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1681929|在Postgres中没有为浮点类型添加精度修饰符的函数round()。只对numeric，根据文件。|unordered-list-item|offset|length|style|CODE|1681930|将浮点数除以numeric，得到double+precision+(float8)。测试：
选择5：：浮动/3：：数值--结果是双精度|1681931|在计算结束时再转一圈。更快更准确。|1681932|永远不要使用date作为列名。它是标准SQL中的保留字，在Postgres中是基类型。|1681933|最好在代码中使用日期文本中的推荐的ISO+8601日期格式。此操作与设置和区域设置无关，而您的本地格式将与不同的设置中断。|1681934|如果不是因为您提到的rest+of+the+query，这可以进一步简化：|unstyled|1681935|SELECT+round((+++sum(CASE+WHEN+acct_no+=+'2999'+THEN+amount+END)
++++++++/+NULLIF(sum(CASE+WHEN+acct_no+=+'3989'+THEN+amount+END),+0)
+++++++)::numeric,+4)+AS+result
FROM+++tbl
WHERE++thedate+between+'2014-1-1'::date+AND+current_date;|1681936|最后，在除数上使用NULLIF()捕获“除法为0”的异常。|1681937|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/current/functions-math.html#FUNCTIONS-MATH-FUNC-TABLE|1|https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-DATE-TABLE^0|0|R|7|11|7|19|4|0|0|6|7|G|G|Y|6|0|0|6|4|0|E|F|1|0|A|H|0|0|9|8|0^^$0|@$1|2|3|4|5|6|7|19|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|1A|8|@$G|1B|H|1C|I|J]|$G|1D|H|1E|I|J]]|9|@$G|1F|H|1G|1|1H]]|A|$]]|$1|K|3|L|5|F|7|1I|8|@$G|1J|H|1K|I|J]|$G|1L|H|1M|I|J]|$G|1N|H|1O|I|J]]|9|@]|A|$]]|$1|M|3|N|5|F|7|1P|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|1Q|8|@$G|1R|H|1S|I|J]]|9|@]|A|$]]|$1|Q|3|R|5|F|7|1T|8|@]|9|@$G|1U|H|1V|1|1W]]|A|$]]|$1|S|3|T|5|U|7|1X|8|@$G|1Y|H|1Z|I|J]]|9|@]|A|$]]|$1|V|3|W|5|6|7|20|8|@]|9|@]|A|$B|C]]|$1|X|3|Y|5|U|7|21|8|@$G|22|H|23|I|J]]|9|@]|A|$]]|$1|Z|3|-4|5|U|7|24|8|@]|9|@]|A|$]]]|10|$11|$5|12|13|14|A|$15|16]]|17|$5|12|13|14|A|$15|18]]]]

<pre class="lang-sql prettyprint-override"><code>SELECT round((
 sum(CASE WHEN acct_no = '2999' 
 AND thedate between '2014-1-1' AND current_date THEN amount END)
 / sum(CASE WHEN acct_no = '3989' 
 AND thedate between '2014-1-1' AND current_date THEN amount END)
 )::numeric, 4) AS result
FROM tbl;
</code></pre>
<ul>
<li>There is no function <code>round()</code> with added precision modifier for floating point types in Postgres. Only for <code>numeric</code>, <a href="https://www.postgresql.org/docs/current/functions-math.html#FUNCTIONS-MATH-FUNC-TABLE" rel="nofollow noreferrer">as per documentation</a>.
</li>
<li>Dividing a floating point number by <code>numeric</code> results in <code>double precision</code> (<code>float8</code>). Test:
<pre><code> SELECT 5::float/3::numeric -- result is double precision
</code></pre>
</li>
<li>Round once at the end of your calculation. That's faster and more accurate.
</li>
<li>Never use <code>date</code> as column name. It's a reserved word in standard SQL and a base type in Postgres.
</li>
<li>It's best to use the <a href="https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-DATE-TABLE" rel="nofollow noreferrer">recommended ISO 8601 date format</a> in date literals in your code. This works irregardless of settings and locale, while your local format would break with different settings.
</li>
</ul>
If it wasn't for the <code>rest of the query</code> you mentioned, this could be simplified further:
<pre class="lang-sql prettyprint-override"><code>SELECT round(( sum(CASE WHEN acct_no = '2999' THEN amount END)
 / NULLIF(sum(CASE WHEN acct_no = '3989' THEN amount END), 0)
 )::numeric, 4) AS result
FROM tbl
WHERE thedate between '2014-1-1'::date AND current_date;
</code></pre>
Finally, this also catches &quot;division by 0&quot; exceptions using <code>NULLIF()</code> on the divisor.

I'm trying to divide two columns in the select statement, then round the quotient to 4 decimal places. 

<pre><code>select round(round(sum(case when acct_no = '2999' 
 and date between '1/1/14' and current_date then amount end)::numeric, 4)::float
 / round(sum(case when acct_no = '3989' 
 and date between '1/1/14' and current_date then amount end)::numeric, 4)::numeric, 4) column
from table
</code></pre>

The rest of the query will hold multiple dates, so the dates in there should be necessary. 

The error it is giving:

<blockquote>
 ERROR: function round(double precision, integer) does not exist
</blockquote>

This was attempted in PostgreSQL.

Rounding the quotient of a division?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我试图在select语句中除以两列，然后将商数除以小数点后的4位。select round(round(sum(case when acct_no = '2999'       and date between '1/1/14' and current_date then amount end)::numeric, 4...

问除法的商数？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问除法的商数？EN