Solved

How can i sum varchar field.

Posted on 2002-07-24
16
1,180 Views
Last Modified: 2012-06-27
I have a field in table OpenTrade varchar(21).
data as follows

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

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

0
Comment
Question by:bcp5190
  • 7
  • 5
  • 3
  • +1
16 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7175507
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7175510
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
0
 

Author Comment

by:bcp5190
ID: 7175617
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:bcp5190
ID: 7175643
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7175647
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 Comment

by:bcp5190
ID: 7175659
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 Comment

by:bcp5190
ID: 7175668
I'm getting following error now
Invalid length parameter passed to the substring function.
0
 

Author Comment

by:bcp5190
ID: 7175674
I'm getting following error now
Invalid length parameter passed to the substring function.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7175687
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 Comment

by:bcp5190
ID: 7175712
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

0
 

Author Comment

by:bcp5190
ID: 7175749
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

0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 20 total points
ID: 7175774
Please try this expression for individual row value:

    CASE WHEN RIGHT(openTrade,2) = 'CR'
         THEN CAST(REPLACE(LEFT(openTrade,LEN(openTrade) - 2),',','') AS DECIMAL(13,2)) * -1
         WHEN RIGHT(openTrade,2) = 'DR'
         THEN CAST(REPLACE(LEFT(openTrade,LEN(openTrade) - 2),',','') AS DECIMAL(13,2))
         WHEN ISNUMERIC(openTrade) = 1
         THEN CAST(REPLACE(openTrade,',','') AS DECIMAL(13,2))
         ELSE 0
    END AS OpenTradeValue    

Or this for a total:
SUM(    CASE WHEN RIGHT(openTrade,2) = 'CR'
         THEN CAST(REPLACE(LEFT(openTrade,LEN(openTrade) - 2),',','') AS DECIMAL(13,2)) * -1
         WHEN RIGHT(openTrade,2) = 'DR'
         THEN CAST(REPLACE(LEFT(openTrade,LEN(openTrade) - 2),',','') AS DECIMAL(13,2))
         WHEN ISNUMERIC(openTrade) = 1
         THEN CAST(REPLACE(openTrade,',','') AS DECIMAL(13,2))
         ELSE 0
    END) AS OpenTradeTotal

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

ALTER TABLE openTrade
    ADD OpenTradeValue AS  
    CASE WHEN RIGHT(openTrade,2) = 'CR'
         THEN CAST(REPLACE(LEFT(openTrade,LEN(openTrade) - 2),',','') AS DECIMAL(13,2)) * -1
         WHEN RIGHT(openTrade,2) = 'DR'
         THEN CAST(REPLACE(LEFT(openTrade,LEN(openTrade) - 2),',','') AS DECIMAL(13,2))
         WHEN ISNUMERIC(openTrade) = 1
         THEN CAST(REPLACE(openTrade,',','') AS DECIMAL(13,2))
         ELSE 0
    END
0
 
LVL 1

Expert Comment

by:killer5
ID: 7176198
i have a comment... can u just have 3 fields instead? :D

Debit, Credit, Amount
0
 
LVL 1

Expert Comment

by:killer5
ID: 7176201
i have a comment... can u just have 3 fields instead? :D

Debit, Credit, Amount
0
 
LVL 1

Expert Comment

by:killer5
ID: 7176202
oops... remove the amount pls. :D just the credit and debit
0
 
LVL 9

Expert Comment

by:miron
ID: 7176217
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
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Upgrading my SSIS package in VS 2012 6 62
SSRS Deployment problem 5 67
SQL, add where clause 5 24
sql 2016 data tools breakdown.. 1 15
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question