We help IT Professionals succeed at work.

COnvert, add and sum in SQL

kdeutsch
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

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

Author

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

Open in new window

Author

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'.
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

Author

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)
UNLOCK SOLUTION

Author

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?

Author

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 Terms of Use and Privacy Policy.