将 varchar 转换为数字和总和会出现错误:
我试图通过将其转换为数字来求和此列,但这些值也有逗号,这是我的查询中的错误来源:
SELECT date_trunc('day', to_date("date" , 'mm-dd-yyyy')) as day,
"from merch",
sum(CAST("amount " AS numeric)) as amount
FROM tb
GROUP BY 1,2
ORDER BY 1,2;
我试图获取每天每个“来自商品”的金额总和。
我得到的错误是:无效数字,值“,”,位置 1,类型:十进制
I am trying to sum this column by casting this as numeric, but the values have commas as well which are the source of error on my query:
SELECT date_trunc('day', to_date("date" , 'mm-dd-yyyy')) as day,
"from merch",
sum(CAST("amount " AS numeric)) as amount
FROM tb
GROUP BY 1,2
ORDER BY 1,2;
I am trying to get the sum of amounts for each "from merch" per day.
Error i get is: Invalid Digit, Value ',', Pos 1, Type: Decimal
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

演示
使用
replace(string,FromValue,ToValue)
注意:这假设您的金额采用 ###,###.00 格式。如果没有,您可能会返回错误的数据。
带小数的演示 2
假设您已正确识别问题。
我(不是最近)在评论中发现了一个幽默的交流:
@xQbert——此后将被称为“xQbert 剃刀”
demo
using
replace(string,FromValue,ToValue)
NOTE: this assumes you have amount in a ###,###.00 format. if not you could return bad data.
Demo 2 with decimal
This is assuming you've identified the problem correctly.
I (not so recently) found an exchange in comments humorous:
@xQbert -- this shall henceforth be referred to as "xQbert's razor"