Convert rate from multiple type of currency to MYR by sql in BigQuery

Question

I want to convert the total rate from multiple type of currency to myr as shown in this table. I want to use sql in Bigquery to convert the amount from usd to myr.

Please help me for this question.

Thanks

Answer 1

You can use case when expression to convert your currency by multiplying with appropriate conversion rate for MYR.

select (case when currency='USD' then amount*4.15 when currency ='MYR' then amount end) Amount from yourtable