samedi 21 mars 2015

SUM in multi-currency

I am trying to do SUM() in a multi-currency setup. The following will demonstrate the problem that I am facing:-



Customer
-------------------------
Id | Name
1 | Mr. A
2 | Mr. B
3 | Mr. C
4 | Mr. D
-------------------------

Item
-------------------------
Id | Name | Cost | Currency
1 | Item 1 | 5 | USD
2 | Item 2 | 2 | EUR
3 | Item 3 | 10 | GBP
4 | Item 4 | 5 | GBP
5 | Item 5 | 50 | AUD
6 | Item 6 | 20 | USD
7 | Item 3 | 10 | EUR
-------------------------

Order
-------------------------
User_Id | Product_Id
1 | 1
2 | 1
1 | 2
3 | 3
1 | 5
1 | 7
1 | 5
2 | 6
3 | 4
4 | 2
-------------------------


Now, I want the output of a SELECT query that lists the Customer Name and the total amount worth of products purchased as:-



Customer Name | Amount
Mr. A | Multiple-currencies
Mr. B | 25 USD
Mr. C | 15 GBP
Mr. D | 2 EUR


So basically, I am looking for a way to add the cost of multiple products under the same customer, if all of them have the same currency, else simply show 'multiple-currencies'. Running the following query will not help:-



SELECT Customer.Name, SUM(Item.Amount) FROM Customer
INNER JOIN Order ON Order.User_Id = Customer.Id
INNER JOIN Item ON Item.Id = Order.Product_Id
GROUP BY Customer.Name


What should my query be? I am using Sqlite


Aucun commentaire:

Enregistrer un commentaire