COALESCE + SUM in MYSQL incorrectly calculating

Question

I am using COALESCE and SUM to get the total value of payments for each invoice. Below is relative data.

SELECT 
    COALESCE(SUM(p.payment_amount),0) as amount_paid 
FROM Payments as p 
WHERE p.invoice = '13923';

Below is database information relative to the issue

invoice   type    payment_amount
13923    Credit    51.19
13923    Check     592.93

The problem is, the total due on the invoice is 644.12. When I manually add the two payment amounts together, it equals 644.12 which is exact payment. But when I run the query and look at the results, it is showing that the payment_amount = 644.1199999999999

Anyone understand how it is coming up with this or see any issues with my code or a better way of doing this?

NOTE: The reason I'm using COALESCE is because sometimes there are payments and sometimes there are not. So I need the value to either be the total amount paid OR 0 if no records found in payments. This code is combined within a longer query but tried posting only relative data.

@GMB - varchar128 ... but even with that, why wouldnt it add correctly? — Web Pro Source, Sep 09 '20 at 15:32

Jason Groulx · Accepted Answer · 2020-09-09T16:06:03.047

2

I believe using the ROUND function would give you the result you're looking for

ROUND(COALESCE(SUM(p.payment_amount),0),2)

After discussing this further in the comments this would be my proposed solution

Update the payment_amount column to DECIMAL(15,2) datatype

CREATE TABLE `Payments` (
  `payment_amount` decimal(15,2) DEFAULT NULL
);

INSERT INTO `Payments` (`payment_amount`) VALUES ('51.19');

INSERT INTO `Payments` (`payment_amount`) VALUES ('592.93');

SELECT SUM(payment_amount) FROM Payments;

Outputs 644.12

edited Sep 09 '20 at 16:06

answered Sep 09 '20 at 15:08

Jason Groulx

400
2
10

I could understand this if I was adding 23.29384 + 294.293495, but trying to understand why adding 592.93 + 51.19 could end up being 644.1199999999999 .... its a pretty straight forward addition statement that SHOULDNT really need to be rounded – Web Pro Source Sep 09 '20 at 15:34
Maybe this SO answer might give you a better understanding of the behavior https://stackoverflow.com/a/51925191/13520068 – Jason Groulx Sep 09 '20 at 15:38
I can understand that for system generated numbers, but these numbers I am showing above are what we are entering for the data. They are manual entered payment amounts from checks. So in a text field, we entered 51.19 and we entered 592.93. Any other explanation on how these could have been represented differently? – Web Pro Source Sep 09 '20 at 15:48
is there a better data type for the 'payment_amount' mysql column that would fix this without having to add round()? It is always going to be dollar amounts ( 293, 297.35, 0.10, etc. ) – Web Pro Source Sep 09 '20 at 15:50
1

Maybe have a look at this post https://stackoverflow.com/questions/13030368/best-data-type-to-store-money-values-in-mysql – Jason Groulx Sep 09 '20 at 15:54

Slava Rozhnev · Answer 2 · 2020-09-09T17:02:44.937

0

You can use CAST for convert sim to decimal and should do COALESCE before SUM:

SELECT 
    SUM(
      CAST(COALESCE(p.payment_amount, 0) AS DECIMAL(12,2))
    ) as amount_paid 
FROM Payments as p 
WHERE p.invoice = '13923';

edited Sep 09 '20 at 17:02

answered Sep 09 '20 at 16:55

Slava Rozhnev

9,510
6
23
39

that is neat. I ended up changing my database column to DECIMAL so I avoided having to do this. But interesting method of conversion. – Web Pro Source Sep 09 '20 at 17:37

COALESCE + SUM in MYSQL incorrectly calculating

2 Answers2