3

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.

enter image description here

O. Jones
  • 103,626
  • 17
  • 118
  • 172
tepmurt
  • 41
  • 1
  • 3
  • 2
    Generally it's not a good idea to do that. Formatting (such as adding currency symbols) should be something you do in the UI where you display the data, not in the raw query – ADyson Sep 08 '20 at 21:08
  • 1
    Short answer: Don't. Format numbers with currency in your application layer. MySQL has zero clue as to how to format numbers properly for currencies where `$x,xxx.xx`, `($x,xxx.xx)`, `$x xxx,xx` and `xx,xx$` are how some locales deal with it. Things get even stranger from there, so forget about trying to figure this out. Find a localization library that can deal with it, learn how to use it, and let the user decide how they want it. – tadman Sep 08 '20 at 21:09
  • If you want to use a dollar sign, why does your code have a pound sign? – Gordon Linoff Sep 08 '20 at 21:20
  • 1
    "but it seems not working" will not help us help you. What makes it "not working"? What is "not working"? No result??? Wrong result??? Error out??? – Eric Sep 08 '20 at 22:15

1 Answers1

5

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172