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.
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.
SQL
Last Comment
kdeutsch
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
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
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.
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,'$',
FROM TABLE1
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,'$',
FROM TABLE1
ow the convert to money....
This code below will work in MSSQL Server.
-Doug
-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
ASKER
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(strtotale
from tblSadPay where intUICID = 3
The error i get is Incorrect syntax near the keyword 'from'.
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(strtotale
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(strtotale
Try this:
Select sum(cast(replace(strtotale
select sum(cast(replace(strTotalE
from tblSadPay where intUICID = 3
from tblSadPay where intUICID = 3
ASKER
This is the error i get from both.
Error converting data type varchar to numeric.
The fields are stored as VarChars
Error converting data type varchar to numeric.
The fields are stored as VarChars
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
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?
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?
ASKER
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.