How can i sum varchar field.

Something like this may do it:

SELECT SUM( CAST(LEFT(openTrade,LEN(openTrade) - 2) AS INT) * CASE WHEN RIGHT(openTrade,2) = 'CR' THEN -1 ELSE 1 END )
FROM table1

I would make it easier, though, by creating a computed column that contained the value and then using that in calculations.  For example:

ALTER TABLE table1
    ADD openTradeValue AS
CAST(LEFT(openTrade,LEN(openTrade) - 2) AS INT) *
    CASE WHEN RIGHT(openTrade,2) = 'CR' THEN -1 ELSE 1 END

Then:

SELECT SUM(openTradeValue) AS 'SumTradeValue'
FROM table1

In first method i'm geting following error

Syntax error converting the varchar value '         671,875.00' to a column of data type int.

In first method i'm geting following error

Syntax error converting the varchar value '         671,875.00' to a column of data type int.

Oh, didn't realize it had commas in it.  Change to this:

SELECT SUM( CAST(REPLACE(LEFT(openTrade,LEN(openTrade) - 2),',','') AS DECIMAL(12,2)) * CASE WHEN RIGHT(openTrade,2) = 'CR' THEN -1 ELSE 1 END )
FROM table1

In first method i'm geting following error

Syntax error converting the varchar value '         671,875.00' to a column of data type int.

I'm getting following error now
Invalid length parameter passed to the substring function.

I'm getting following error now
Invalid length parameter passed to the substring function.

Do you have some columns that are empty or don't have CR/DR?  Your initial posting of data made it look as if the data was consistent so I assumed that it was.  If it's not, you will have to add another CASE to test for an empty column and/or no CR/DR on the end.

I'm sorry but here is my actual data
OpenTrade
---------
XXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXX
0.00
0.00
471,072,451.94CR
991,154,117.94DR
771,072,952.94CR
191,174,987.94DR
271,074,551.94CR
291,173,387.94DR

I'm sorry but here is my actual data
OpenTrade
---------
XXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXX
0.00
0.00
471,072,451.94CR
991,154,117.94DR
771,072,952.94CR
191,174,987.94DR
271,074,551.94CR
291,173,387.94DR

i have a comment... can u just have 3 fields instead? :D

Debit, Credit, Amount

i have a comment... can u just have 3 fields instead? :D

Debit, Credit, Amount

oops... remove the amount pls. :D just the credit and debit

bcp,

are teh XXXX characters also part of data or it was just a way to beautify the presentation. It's kind of important. Now, if it is part of data how do you want it to be treated, as 0.00 ( ZERO ) or your appplication prefers it to be treated as NULL.

Thanks for your comments beforehand.

miron