Toad World® Forums

Sum of Products by Customer

Trying to get sum of all the products( datatype varchar) a customer has bought , by customer name . But SUM function from transform and cleanse if not working with varchar column .

if there any other function available ?

I don’t think you can use SUM on a varchar. Have you used a to_number function to convert the data?

-Mark

You have to convert column type to one of numeric type. You should apply Convert Datatype rule before counting the SUM.

Aleksey

sum(cast(varcharFieldOfNumbers as Int))

This function in transform and cleanse doesn’t work–Sum(cast([PRODUCT_NAME] as Int), CUSTOMER_ID)–

geting error message “ORA-00936: missing expression”

I gave a Sql expression. Not sure about equivalent in transform and cleanse but if the syntax is the same then what you have is wrong. cast([PRODUCT_NAME] as Int) is fine but there is no comma in the Sum function. So there should be 2 parantheses aftert the “as Int))”. If Customer_ID is the next field I don’t know why there is a “)” after it as it is currently ending the Sum function.