Link to home
Start Free TrialLog in
Avatar of kdeutsch
kdeutschFlag for United States of America

asked on

COnvert, add and sum in SQL

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.
Avatar of OnALearningCurve
OnALearningCurve

Can you post what you have so far?
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
Avatar of kdeutsch

ASKER

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

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
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
This is the error i get from both.
Error converting data type varchar to numeric.

The fields are stored as VarChars
ASKER CERTIFIED SOLUTION
Avatar of dev2dev
dev2dev
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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?
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.