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
When transfer.trf_type = 'I' then 'Transfer From the inside'
When transfer.trf_type = 'o' then 'Transfer fom the outside'
End AS type ,
Group By transfer.trf_type
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;