CODEWITHSUNDEEP
mysqlsql
mohamed nur
mohamed nur

Reputation: 331

How to add percentages and insert the result into different table

I am trying to provide selling rate and buying rate from the spot rate. At the moment I have a table called Currency which holds [currency_code] and [Rate]. I also have table called Transaction that contains [Tran_ID], [Buying_rate] and [Selling_rate] I would like to add 3 % from the [Rate] and store it into the [Buying_rate]. I would also like to minus 3% and store it into the [Selling_rate].

enter image description here

I have looked for tutorials online and I was unable to find any. I would appreciate lot if a demonstration can be shown.

I have applied the query 

 insert into Transaction Set 
 Tran_id = 1,
 Buying_Rate = (select (rate + (3*Rate)/100) as ratepos from currency  ),
 Selling_Rate = (select (rate - (3*Rate)/100) as rateneg from currency  )

I get this error message =>>> #1242 - Subquery returns more than 1 row

Table structure of Transaction

Field        Type    Collation  Attributes  Null    Default Extra   Action
Tran_ID      int(11)            No  None    auto_increment                          
Buying_Rate  float          No  None                                
Selling_Rate float          No  None

Table structure of currency

Field          Type   Collation     Attributes  Null    Default Extra   Action
currency_code  varchar(255)   latin1_swedish_ci No                                  
Rate           float                            Yes       NULL

On the Transaction table its meant to be empty so that the new selling and buying rate get stored in their an example of the output would be

On the Transaction table its meant to be empty so that the new selling and buying rate get stored in their an example of the output would be 

**|Tran_ID|Buying_rate|Selling_Rate| ** 
  | 1     |   1.1842  | 1.1242     |

the example above shows the EURO currency the adding of the 3% and the subtracting of 3%

Upvotes: 1

Views: 2123

Answers (2)

echo_Me
echo_Me

Reputation: 37253

just an idea on how to do it

 insert into Transaction Set 
 Trans_id = your_transaction_id ,
 Buying_ID = (select (rate + (3*Rate)/100) as ratepos from Currency 
              where currency_code = 'euro'),
 Selling_ID = (select (rate - (3*Rate)/100) as rateneg from Currency 
               where currency_code = 'euro' )

EDIT.

demo here

Upvotes: 2

Ken Clark
Ken Clark

Reputation: 2530

You can do like this:

Insert into Transaction  
Select Top 1 'Enter Trans_id', (Rate + (3*Rate)/100),(Rate - (3*Rate)/100) From Currency

Upvotes: 1

Related Questions