CODEWITHSUNDEEP
mysqlroundingrounding-error
HTMLGuy
HTMLGuy

Reputation: 241

MYSQL Rounding issue

WHY IS THIS HAPPENING?

SELECT 
c.tax_rate, 
c.line_item_total_price, 
c.shipping_total_price, 
ROUND((c.tax_rate * 
(c.line_item_total_price+c.shipping_total_price)),2), 
(c.tax_rate * (c.line_item_total_price+c.shipping_total_price)) 
FROM carts c
WHERE c.id = 323002;

returns:

.07
925.00
62.50
69.12
69.125

But this:

SELECT 
ROUND((.07 * (925.00+62.50)),2), 
(.07 * (925.00+62.50));

Returns the correct:

69.13
69.1250

Why the extra 0 at the end of that one?

FYI: Shipping and line item are DECIMAL(10,2) and tax is DOUBLE

Upvotes: 0

Views: 62

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31792

Try 

SELECT 1.00 * 1.0

You will get 1.000. This is because the exact result for DECIMAL(a,b) * DECIMAL(c,d) needs a DECIMAL(a+c,b+d) as data type.

Upvotes: 0

Adam H
Adam H

Reputation: 1818

I don't know a lot about MySQL but I would guess that it's an implicit conversion thing based on the column types. Maybe try casting everything until you find out what one was giving you the problem. DECIMAL(10,2) only allows 2 decimal places (that's what the 2 is)

Upvotes: 1

Related Questions