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

CodePudding user response：

demo

using replace(string,FromValue,ToValue)

NOTE: this assumes you have amount in a ###,###.00 format. if not you could return bad data.

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;

Demo 2 with decimal

sum(CAST(replace("amount",',','') AS decimal(10,2))) as amount 

This is assuming you've identified the problem correctly.

I (not so recently) found an exchange in comments humorous:

• If you EVER need to do math on it, store it as a number
• If you'll NEVER do math on it store it as string
• If it needs to do both, you need two columns
• Except for dates... store dates as dates period and use date functions on them... not string {shudder} functions!
• and AutoIncrements can be numbers (though we should never do math on them)

@xQbert -- this shall henceforth be referred to as "xQbert's razor"