mysql: sum() conditions

I have the following table:

+----+-----+--------+----------+
| id | uid | amount | currency |
+----+-----+--------+----------+
|  1 |   1 |      5 | us       |
|  2 |   1 |      5 | eu       |
|  3 |   1 |      2 | us       |
|  4 |   1 |      3 | eu       |
|  5 |   2 |      8 | us       |
|  6 |   2 |     10 | eu       |
|  7 |   1 |      3 | us       |
|  8 |   2 |      4 | eu       |
+----+-----+--------+----------+
8 rows in set (0.00 sec)

Where I had to get the total sales (amount) for each user, the problem is there: the amounts have different currencies. So I need to convert them into 1 currency then sort them… all in 1 query to conserve resources. After pondering and, google’ing a friend of mine helped me come up with this solution:

SELECT uid, SUM(
	CASE currency
		WHEN 'us' THEN amount
		WHEN 'eu' THEN amount / 1.35
	END
	) as total FROM sales
GROUP BY uid ORDER BY total DESC;

Where 1.35 is the hypothetical exchange rate: we convert “eu” value to “us“ then sort them from highest to lowest. So the above query will return this result:

+-----+---------+
| uid | total   |
+-----+---------+
|   2 | 18.3704 |
|   1 | 15.9259 |
+-----+---------+
2 rows in set (0.00 sec)

Tagged as: code, sql