Search code examples
sqlpostgresqlgroup-bycasting

converting varchar to number and sum gives error:

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

Solution

  • 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"