We help IT Professionals succeed at work.

# COnvert, add and sum in SQL

kdeutsch asked
on
417 Views
Last Modified: 2010-03-20
What I need to accomplish is to first convert the information to get rid of the \$ sign then add the 2 fields together to get a total and then to total the sum of all records in the db.
Here is what infor looks like.
\$405.67
\$607.00 and so on.
The fields I need to convert are strTotalEntitlements, strttwh AND strMilesPay.
Then I need to minus strTTWH from strTotalEntitlements and add strMilesPay and then sum every record for a grand total of all records.  I think I have the where clause it jus tthe rest of the equation.
Comment
Watch Question

## View Solution Only

CERTIFIED EXPERT

Commented:
Can you post what you have so far?
CERTIFIED EXPERT

Commented:
Convert to a money type, that way you don't need to parse out the '\$', it will be automatic.
Something like this:

select sum(convert(money, strTotalEntitlements) + convert(money, strtwh)) as Added,
sum(convert(money, strTotalEntitlements) - convert(money, strtwh) + convert(money, strMilesPay)) as Other
from myTable
group by strTotalEntitlements, strtwh, strMilesPay

Commented:
Snarf0001,
The problem lise in that when I print it to a text fiel i can't have the dollar sign in there,  because its imported into a mainframe.

Commented:
Hi,

what is exactly strTotalEntitlements, strttwh AND strMilesPay and all ???
from the names I guess they are variables in your program/application

ok, from my understanding if you have 3 colums say col1, col2 and col3 and they are dollar amounts and you want to remove it from all three columns, add values in three column (col+col2+col3) then get grand total

if that is the case then try this

assuming your table name is table1 and col1,col2 and col3 are columns in that table

SELECT SUM(CAST(REPLACE(COL1,'\$','')) + CAST(REPLACE(COL2,'\$','')) + CAST(REPLACE(COL3,'\$','')) )
FROM TABLE1

Commented:
ow the convert to money....

Commented:
This code below will work in MSSQL Server.

-Doug

``````select SUM(cast(replace(strtotalentitlements,'\$','') as decimal(10,2))-cast(replace(strttwh,'\$','') as decimal(10,2))+cast(replace(strMilesPay,'\$','') as decimal(10,2)))   from myTable
``````

Commented:
ok, I have tried all but get the follwoing erros,
DougVarga,
Error: Error converting data type varchar to numeric.

Dev2Dev:
Error:Incorrect syntax near 'CAST', expected 'AS'.

After looking at my database closer I see I am doing thing harder than I have to, I have a field that is already totaled that is called strTotalCheck.  But when i add my where clause to it it does not total all the records liek I thought is there something different i need to do than sum.
SO if i have 8 records it should total up the 8 records. Here is what I have so far.

Select sum(cast(replace(strtotalentitlement,'\$','') as ALLTotals
from tblSadPay where intUICID = 3

The error i get is Incorrect syntax near the keyword 'from'.

Commented:
You're missing some parenthesis.  And the data type you're casting to.

Try this:

Select sum(cast(replace(strtotalentitlement,'\$','') as decimal(10,0))) as ALLTotals from tblSadPay where intUICID = 3

Commented:
select sum(cast(replace(strTotalEntitlements,'\$','')as decimal(12,3))+ cast(replace(strttwh,'\$','')as decimal(12,3)) + cast(replace(strMilesPay,'\$','')as decimal(12,3))) as grand_total
from tblSadPay where intUICID = 3

Commented:
This is the error i get from both.
Error converting data type varchar to numeric.

The fields are stored as VarChars
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)

Commented:
Here is some data.   All this need to be summed up and converted to
strtotalcheck
--------------------
\$363.64   (Need to look like  36364)
\$754.89
\$950.35
\$369.54
Total to equal 243842

Commented:
i was asking to give some sample rows out of the query i gave above

if it results any rows then that means your dollar amounts have some weired characters to be striped out

btw:

what do you mean by

\$363.64   (Need to look like  36364)

you want the x amount to be multiplied by 100?

Commented:
The query runs but returns nothing, the inUICID has 5 entries but none return.  I changed inUICID to IntPayId which is the key and it still returned nothing.
Unlock the solution to this question.
##### Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the