sql - 在sql中转换的传输总和

Question

我有一个保存客户转账的表格。我想要一个以基础货币获取转账总额的 sql 查询。例如，如果转账是以美元进行的，我必须以基础货币获取此金额。我有另一个表存储货币并处理货币之间的转换率。如何使用单个 sql 查询获取基础货币的转账总和？

表格如下

1. Transfer(transfer_id , trf_type , currency (references currency) , amount)
2. 货币(Currency , Rate) 其中汇率指的是基础货币

这是我尝试但未处理货币转换的方法:

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

我该怎么做？上述查询的输出如下:

Answer 1

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;