Abdalla Ismail Abdalla Ismail - 5 days ago 7
SQL Question

sum of transfers with conversion in sql

i have a table that saves customers transfers. I want an sql query that gets the total amount of transfers in base currency.For example , if the transfer was made in dollars , i have to get this amount in the base currency. I have another table that stores currencies and handles the conversion rates between the currencies.
How can i get the sum of transfers in the base currency using a single sql query?

The tables are as follows


  1. Transfer(transfer_id , trf_type , currency (references currency) , amount)

  2. Currency (Currency , Rate) Where rate refers to the base currency



Here's what i tried but without handling currency conversion:

Select
Case
When transfer.trf_type = 'I' then 'Transfer From the inside'
When transfer.trf_type = 'o' then 'Transfer fom the outside'

End AS type ,
Count(transfer.trf_type) ,
Sum(transfer.amount)
From transfer
Group By transfer.trf_type


How can i do this? The output of the above query is as follows:
enter image description here

Answer
select 
    case
        when transfer.trf_type = 'I' then 'Transfer From the inside'
        when transfer.trf_type = 'o' then 'Transfer fom the outside'
    end AS type,
    count(transfer.trf_type),
    sum(transfer.amount * currency.rate)
from transfer
inner join currency using(currency)
group By transfer.trf_type;
Comments