mySQL receive unexpected result for .99 times 5

Question

From @manix and @Matt Crandall's suggestions, I found out that .toFixed(2) solves my problem. I'll dig deeper into the why later. Case closed.

I'm building a database for a store and get wrong results whenever I get an order of 5 for an item that has .99 as price tag.

SQL code:

DROP DATABASE IF EXISTS store;
CREATE DATABASE store;

USE store;

CREATE TABLE products (
    id INT(11) AUTO_INCREMENT NOT NULL,
    name VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO products
    (name, price)
VALUES 
    ('Some item', 39.99);

NodeJS code:

var mysql = require('mysql');

var connection = mysql.createConnection({
  host: 'localhost',
  port: XXXX,
  user: 'XXXX',
  password: 'XXXX',
  database: 'store'
});

connection.connect((err) => {
  if (err) throw err;
  totalCost(1, 5);
});

function totalCost(id, quantity) {
    connection.query('SELECT price FROM products WHERE id = "' + id + '"', (err, res) => {
      if (err) throw err;

      var price = res[0].price;
      var total = price * quantity;

      console.log('Price: $' + price + '\nTotal: $' + total);
    });
  }

For this example, the total is 199.95000000000002 whilst it should be 199.95. Why the trailing numbers? How do it fix this?

I'm sorry if this is a duplicate question. I've looked but couldn't find any similar question (maybe I used wrong keywords for my search).

[floating point number precision](https://stackoverflow.com/questions/1458633/how-to-deal-with-floating-point-number-precision-in-javascript), maybe? — manix, Oct 24 '17 at 16:39
Is this perhaps what you are looking for? https://stackoverflow.com/a/13292833/1607393 — Aszula, Oct 24 '17 at 16:48
Q: How do it fix this? A: Better understand floating point numbers and how they work: [What every computer scientist should know about Floating Point Numbers](https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html). And yes, in this particular case, "toFixed():" is a good solution. — paulsm4, Oct 24 '17 at 17:01

mySQL receive unexpected result for .99 times 5

0 Answers0