• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1196

How can i sum varchar field.

I have a field in table OpenTrade varchar(21).
data as follows

---------
12332CR
12222DR
10000CR
10123CR
34133CR
10000CR

Now how can i add this if CR means Minus and DR minus +

0
bcp5190
• 7
• 5
• 3
• +1
1 Solution

Senior DBACommented:
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

0

Senior DBACommented:
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
CASE WHEN RIGHT(openTrade,2) = 'CR' THEN -1 ELSE 1 END

Then:

FROM table1
0

Author Commented:
In first method i'm geting following error

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

Author Commented:
In first method i'm geting following error

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

Senior DBACommented:
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

0

Author Commented:
In first method i'm geting following error

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

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

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

Senior DBACommented:
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.
0

Author Commented:
I'm sorry but here is my actual data
---------
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

0

Author Commented:
I'm sorry but here is my actual data
---------
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

0

Senior DBACommented:
Please try this expression for individual row value:

ELSE 0

Or this for a total:
SUM(    CASE WHEN RIGHT(openTrade,2) = 'CR'
ELSE 0

I would strongly consider making this a computed column, like so:

ELSE 0
END
0

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

Debit, Credit, Amount
0

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

Debit, Credit, Amount
0

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

Commented:
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.

miron
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.