我試圖通過將此列轉換為數字來對這一列求和，但這些值也有逗號，這是我查詢的錯誤來源：
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，型別：十進制
uj5u.com熱心網友回復：
演示
使用replace(string,FromValue,ToValue)
注意：這假設您有 ###,###.00 格式的金額。如果不是，您可能會回傳錯誤資料。
with tb AS
(SELECT '1,000.01' as "amount", '01-01-2022' as "date",'z' as "from merch" UNION ALL
SELECT '10,000.02' as "amount", '01-01-2022' as "date",'z' as "from merch" UNION ALL
SELECT '100,000.02' as "amount", '02-01-2022' as "date",'y' as "from merch")
SELECT date_trunc('day', to_date("date" , 'mm-dd-yyyy')) as day,
"from merch",
sum(CAST(replace("amount",',','') AS numeric)) as amount
FROM tb
GROUP BY 1,2
ORDER BY 1,2;
演示 2 帶小數
sum(CAST(replace("amount",',','') AS decimal(10,2))) as amount
這是假設您已正確識別問題。
我（不是最近）在幽默的評論中發現了一個交流：
- 如果您需要對其進行數學運算，請將其存盤為數字
- 如果您永遠不會對其進行數學運算，請將其存盤為字串
- 如果兩者都需要，則需要兩列
- 除了日期...將日期存盤為日期期間并在其上使用日期函式...不是字串 {shudder} 函式！
- 并且 AutoIncrements 可以是數字（盡管我們永遠不應該對它們進行數學運算）
@xQbert - 此后將被稱為“xQbert 剃刀”
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/446048.html
標籤：sql PostgreSQL 通过...分组 铸件