1

I have an issue with the SUM-Function in my MySQL Workbench. When I use the function, it returns a false value. I´d like to SUM these three numbers:

  • 56,03
  • 35,59
  • 54,35

The result should be 145,97, but its just 145 instead. I tried these different codes:

SELECT SUM(price) FROM table;

This one returns the value 145. 

SELECT ROUND(SUM(price),2) FROM table;

The second one returns the value 145.00.

I was wondering whats wrong with the code, because I tried it in another DB that I have in my MySQL Workbench. Also tried it over the Terminal in both databases. In the other database the function works correctly.

Lelio Faieta
  • 6,457
  • 7
  • 40
  • 74
  • 2
    what is the format for the price field? in mysql number use `.`(dot) for decimal separator and not `,`(comma) as in your example data – Lelio Faieta Mar 22 '17 at 16:54
  • 2
    Are you storing the values as strings like here: http://rextester.com/YNEAG27721 ? – Paul Spiegel Mar 22 '17 at 16:57
  • Yes I store them in Strings, because for some reasons I could not store the data as decimal. In the other DB it works fine with strings. –  Mar 22 '17 at 17:04
  • My first guess would also be what @LelioFaieta suggested. I don't have an instance of mysql available to me right now to test, but I don't believe `mysql` even supports using commas as a decimal place separator (I could be wrong on that though). My guess would be that it is interpreting your values as `varchar` then when it converts to numeric, it converts only the first numeric values until it hits the comma. – gmiley Mar 22 '17 at 17:05
  • 1
    What is "the other DB"? – Paul Spiegel Mar 22 '17 at 17:06
  • if the data type is set as decimal then the decimal will be after a `.` See also [this question](http://stackoverflow.com/questions/8669212/change-decimal-separator-in-mysql) as example. – Lelio Faieta Mar 22 '17 at 17:08
  • @PaulSpiegel Another database where I stored decimal numbers as a String and the sum function works. Same Code. –  Mar 22 '17 at 18:20

1 Answers1

0

Best thing to do would be to change your table so that it is a decimal field.

However, if you can't do that, the following query should get what you want:

SELECT SUM(replace(price,',', '.')) FROM tbl;

You can test in on SQLFiddle

Kevin
  • 7,162
  • 11
  • 46
  • 70
  • Yes it works! Big thank you. It´s still a workaround and I still try to figure out how to store my numbers as a decimal! –  Mar 22 '17 at 18:25