Search code examples
mysqlcoalesce

COALESCE + SUM in MYSQL incorrectly calculating

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.

Solution

  • 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