Sum of Products by Customer

Toad Data Point & Toad Intelligence Central
1

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 ?

2

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

-Mark

3

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

Aleksey

4

sum(cast(varcharFieldOfNumbers as Int))

5

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

geting error message “ORA-00936: missing expression”

6

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.