Solved

How can i sum varchar field.

Posted on 2002-07-24
16
1,177 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:ScottPletcher
Comment Utility
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:ScottPletcher
Comment Utility
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
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
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
Comment Utility
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
Comment Utility
I'm getting following error now
Invalid length parameter passed to the substring function.
0
 

Author Comment

by:bcp5190
Comment Utility
I'm getting following error now
Invalid length parameter passed to the substring function.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
Comment Utility
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
Comment Utility
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:
ScottPletcher earned 20 total points
Comment Utility
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
Comment Utility
i have a comment... can u just have 3 fields instead? :D

Debit, Credit, Amount
0
 
LVL 1

Expert Comment

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

Debit, Credit, Amount
0
 
LVL 1

Expert Comment

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

Expert Comment

by:miron
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now