Formatting MySQL results as currency

Question

What will be the best way to include the result with dollar sign?

I was using something like CONCAT('£', SUM(Deposits)) AS TotalDeposits but it seems not working.

Answer 1

MySQL boasts a FORMAT() function for doing the number formatting part of this.

SET @amount := 123456.7890;
SELECT CONCAT('$',FORMAT(@amount,2,'en_US')),
       CONCAT('€',FORMAT(@amount,2,'de_DE'));

gets you the US and DE formatting. You have to put in the currency symbol yourself; MySQL's locale handling doesn't throw it in for you.

| ------------------------------------- | ------------------------------------- |
| $123,456.79                           | €123.456,79                           |
| ------------------------------------- | ------------------------------------- |

If you're dealing with other peoples' money, you may want to use a data type like DECIMAL(20,2) rather than a FLOAT or DOUBLE so you don't get floating-point-style arithmetic rounding errors in aggregate functions like SUM().

That all being said: wise programmers often handle this kind of formatting in their application programs rather than their databases, as @ADyson pointed out in his comment. That's because application programming languages have more complete locale handling than databases (at any rate MySQL) and you can control it better.