CODEWITHSUNDEEP
sql-servert-sql
Geo
Geo

Reputation: 336

Using Sum(Cast(Replace syntax

I am trying to write a simple query in order to change some of our stage data. I have a varchar $ column (unfortunately) that needs to be summed. My issue is that because of commas, I cannot change the datatype. So, I can use REPLACE(AMT,',','') to remove the commas but It still wont let me cast it as a decimal and I get

Error converting data type varchar to numeric.

I am trying the following below with no luck. Any ideas? Can this be done or am I using the wrong syntax here?

Select SUM(Cast(REPLACE(Amt,',','') as Decimal (18,2)) )

Upvotes: 0

Views: 4757

Answers (3)

SQALEX101
SQALEX101

Reputation: 229

I wouldn't use money as a data type as it is notorious for rounding error.

The error is due to SQL order of operations within your SUM(CAST(REPLACE... operation. This issue can be resolved by summing the column AFTER it's been staged to be summed via a subquery: 

SELECT SUM(Field),... 
FROM ( SELECT 
       Cast(REPLACE(Amt,',','') as NUMERIC) as 'Field' 
       ,...
     ) [Q]

If the table you're summing is administered by a BI Team, get them to stage the data there. Happy Data Happy life.

Upvotes: 0

Geo
Geo

Reputation: 336

I was able to resolve this with @HABO suggestion. I used Cast(Ltrim(rtrim(table.AMT)) as Money) for all instances of the varchar amount. This removed white space and removed the commas from the numbers.

Upvotes: 1

C. White
C. White

Reputation: 800

This should work... including an example.

Edit: if you are on SQL Server 2012+, you may be able to shorten your task by using Try_Convert

DECLARE @SomeTable AS TABLE (Amt Varchar(100));
INSERT INTO @Sometable (Amt) VALUES ('abc123,456.01'),('  123,456.78 '),(Null),('asdad'),('');

With NumericsOnly AS 
  (
    SELECT
      REPLACE(Left(SubString(Amt, PatIndex('%[0-9.-,]%', Amt), 8000), PatIndex('%[^0-9.,-]%', SubString(Amt, PatIndex('%[0-9.,-]%', Amt), 8000) + 'X')-1),',','') AS CleanAmt
    FROM
      @SomeTable
  )

  SELECT
    SUM(CONVERT(DECIMAL(18,2), CleanAmt)) AS TotalAmt
  FROM
    NumericsOnly
  WHERE
    IsNumeric(CleanAmt)=1

General methodology is taken from here

Upvotes: 0

Related Questions