CODEWITHSUNDEEP
mysql
maxisme
maxisme

Reputation: 4245

Summing based on different currencies

I am looking to sum the total price in GBP SUM(price) but I need to convert USD to GBP by multiplying by 0.74

+---------+-------+
| country | price |
+---------+-------+
| GBP     |     1 |
| USD     |     2 |
| GBP     |     3 |
| USD     |     4 |
+---------+-------+

I have tried this SQL:

SELECT
    IF(`country` = 'USD',
        SUM(price * 0.74), 
        SUM(price)
    ) AS price
FROM `products`

But it is returning the error:

this is incompatible with sql_mode=only_full_group_by

Upvotes: 0

Views: 60

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521279

Just take a sum of prices over the entire table, and scale the USD prices to GBP by multiplying 0.74, otherwise use the original value for GBP:

SELECT
    SUM(CASE WHEN country = 'USD' THEN 0.74 * price ELSE price END) AS price
FROM products;

enter image description here

Demo

A nicer way to handle forex rates would be to have a separate table which converts any currency into USD and back again. Then, you could simply do a join to get the result you wanted, rather than having to hard code a forex rate into the query.

Upvotes: 2

Related Questions