CODEWITHSUNDEEP
mysqlsql
Lansana Camara
Lansana Camara

Reputation: 9873

SUM on VARCHAR field converted to DECIMAL not adding numbers after decimal

I have a VARCHAR field that stores a value like 0.00000000.

I want to run a report query to SUM all those VARCHAR fields, which means I have to convert them to a number to add them.

Here's my query, which works as far as giving no errors, but it gives the wrong number back:

SELECT SUM(CAST(IFNULL(tx.received_amount, '0.00000000') AS DECIMAL(16, 16)))
FROM account
JOIN account_invoice
    ON account_invoice.account_id = account.id
JOIN withdrawal
    ON withdrawal.invoice_id = account_invoice.invoice_id
JOIN tx
    ON tx.id = withdrawal.tx_id
    AND tx.currency = 'BTC'
    AND tx.created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)
WHERE account.id = 1

This is what I get: 100 x 1.12345678 = 100.00000000

This is what I should get: 100 x 1.12345678 = 112.34567800

Why is the SUM not adding the numbers after the decimal?

Upvotes: 0

Views: 1368

Answers (3)

GMB
GMB

Reputation: 222442

You are not using the DECIMAL datatype accordingly to your use case. DECIMAL(16, 16) declares a decimal number with a total of 16 digits and with 16 decimal digits. This cannot hold a value greater than 1.

Consider:

SELECT CAST('1.12345678' AS DECIMAL(16, 16))

Returns: 0.9999999999999999.

You probably want something like DECIMAL(16, 8) instead, since your strings seem to have 8 decimals.

From the MySQL documentation:

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments are as follows:

  • M is the maximum number of digits (the precision). It has a range of 1 to 65.

  • D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.

Upvotes: 3

Nosajimiki
Nosajimiki

Reputation: 1103

GMB's answer is usually the best choice, but if you truly need to output a (a_really_precise_number)*100 you can do it application-side by actually passing it as a string into a language that supports arbitrarily large numbers, then cast it application side. If you have numbers more precise than 16 digits in your database, you are likely already using one that supports this in your application.

In some cases, you are looking at data from another source and you have more precise numbers than your language of choice is designed for. Many languages that don't support these larger numbers natively may have libraries available that do fancy parsing to perform math on strings as strings but they tend to be a bit slow if you need to work with really large numbers or data sets.

A third option if you are just multiplying it by a power of 10 such as N*100 and outputting the result is to pass it to the application as a string, then just parse it to move that decimal over 2 places like this:

function shiftDec(str, shift){
  // split on decimal point
  var decPoint = str.indexOf(".");
  var decInt = str.substr(0, decPoint);
  var decMod = str.substr((decPoint+1));

  // move decimal 'shift' places to simulate N*100.
  if(shift > 0){
    var shiftCopy = decInt .substr(0,shift);
    decInt = decInt + shiftCopy;
    decMod = decMod .substr(shift);
  } else {
    var shiftCopy = decInt .substr((decInt.length + shift));
    decInt = decInt .substr(0,(decInt.length + shift));
    decMod = shiftCopy + decMod;
  }
  return decInt + '.' + decMod;
}
var result = shiftDec("1234567891234567.8912345678912345", 2);
document.write(result);

Upvotes: 1

fifonik
fifonik

Reputation: 1606

You should not use DECIMAL(16,16)

SELECT 100 * CAST('1.123' AS DECIMAL(16,16))

99.999...

SELECT 100 * CAST('1.123' AS DECIMAL(16, 10))

112.300...

Upvotes: 0

Related Questions