How can I add two field values and insert them to a single column?

Question

I have a table:

MTCN       Number        RecPrincipalAmount        TotalCharges
123        APK123            506.21                   258.22

Now I want to add RecPrincipalAmount and TotalCharges and insert them into PrincipalAmount.

I want this:

MTCN       Number        PrincipalAmount        
123        APK123            764.43

I want to display all these data in Crystal Reports.

[What have you tried?](http://mattgemmell.com/2008/12/08/what-have-you-tried/) — Some programmer dude, Mar 06 '13 at 09:17
Welcome to StackOverflow, I formatted your question for you (the first one's free ;) ). Why is this tagged with [tag:c++] rather than [tag:sql]? Can you provide [your attempt at doing this](http://whathaveyoutried.com) so that we can help you fix it? — johnsyweb, Mar 06 '13 at 09:18
Urgency isn't really a factor in this community. We aim to provide quality questions and answers that will help people for years to come. Can you please answer the questions in my previous comment? — johnsyweb, Mar 06 '13 at 09:20

Mikael Eriksson · Accepted Answer · 2013-03-06T09:29:05.890

1

select MTCN,
       Number,
       RecPrincipalAmount+TotalCharges as PrincipalAmount
from YourTable

You can cast your varchar to float before adding.

select MTCN,
       Number,
       cast(RecPrincipalAmount as float)+cast(TotalCharges as float) as PrincipalAmount
from YourTable

or money if that is more appropriate.

select MTCN,
       Number,
       cast(RecPrincipalAmount as money)+cast(TotalCharges as money) as PrincipalAmount
from YourTable

edited Mar 06 '13 at 09:29

answered Mar 06 '13 at 09:18

Mikael Eriksson

i have done it. its not working it gives me the joined values not sum of the values. – Sameen Yamin Mar 06 '13 at 09:21
What is the datatype of your columns – Mikael Eriksson Mar 06 '13 at 09:21
@johnsyweb i need it urgent thats why i said. this is first time im posting a question to any website so i was unaware of tags. it was done by mistake – Sameen Yamin Mar 06 '13 at 09:27
`varchar` is an *interesting* [selection for a column containing monetary values](http://stackoverflow.com/q/582797/78845)! – johnsyweb Mar 06 '13 at 09:27
1

@SameenYameen You should change the design of your database to use the appropriate datatype for your columns. The code I have suggested will fail if you have values in your columns that can not be converted. – Mikael Eriksson Mar 06 '13 at 09:31
@SameenYameen: I see. I have fixed the tags for you but you need to read http://stackoverflow.com/faq/#howtoask before posting any more questions. – johnsyweb Mar 06 '13 at 09:31
@MikaelEriksson OMG now it is working properly.. Bundle of thanks :) – Sameen Yamin Mar 06 '13 at 09:35
@SameenYameen: Don't forget to [mark it as the accepted answer by clicking on the check box outline to the left of the answer.](http://stackoverflow.com/faq/#howtoask) – johnsyweb Mar 06 '13 at 09:39

score 0 · Answer 2 · answered Mar 06 '13 at 09:38

0

Try to use this.

SELECT 
   MTCN,
   Number,
   CONVERT(FLOAT, RecPrincipalAmount) + CONVERT(FLOAT, TotalCharges) PrincipalAmount
FROM TableName

answered Mar 06 '13 at 09:38

Nitesh Kumar

score 0 · Answer 3 · answered Mar 07 '13 at 14:12

0

Use a formula field:

//{@Principal}
{table.RecPrincipalAmount} + {table.TotalCharges}

answered Mar 07 '13 at 14:12

craig

score -1 · Answer 4 · answered Mar 06 '13 at 09:29

-1

SELECT SUM(RecPrincipalAmount + TotalCharges ) as "You Column"
FROM Your_Table;

answered Mar 06 '13 at 09:29

Saurabh B

4 Answers4