How to apply sum() query in Presto SQL and facing unexpected parameters (varchar) for function sum

I am trying to run the sum query in presto SQL, and I am facing below error message. In respect of the ae.amount and ae.type both are string, not sure what stopping to execute the query.

PrestoUserError: PrestoUserError(type=USER_ERROR, name=SYNTAX_ERROR, message="line 3:1: Unexpected parameters (varchar) for function sum. Expected: sum(double) , sum(real) , sum(bigint) , sum(interval day to second) , sum(interval year to month) , sum(decimal(p,s)) "

SELECT SUM(ae.amount) filter (where ae.type IN ('USA')) FROM db_1.table1

Below works well

SELECT SUM(CAST(ae.amount as DECIMAL(13,2))) filter (where ae.type IN ('USA')) FROM db_1.table1

Solution

Based on the error message, ae.amount is a string rather than a number. You need to cast it to a number. It is not clear from your question what type, but let's say an int:

SELECT SUM(CAST(ae.amount as INT)) filter (where ae.type IN ('USA'))
FROM db_1.table1

If this gets a conversion error you can find the rows that fail using:

SELECT ae.amount
FROM db_1.table1
WHERE ae.type IN ('USA') AND
      TRY_CAST(ae.amount as INT) IS NULL AND
      ae.amount IS NOT NULL