SQL SUM Returning Incorrect Result

I’m trying to get the SUM from the columns returned by a subquery but the value I’m getting back is not what I’m expecting.

When I run this query:

SELECT
    totals.repair_order_amount_total
FROM
    (
        SELECT
            auto_repair_order.id,
            auto_repair_order.repair_order_amount_total
        FROM
            auto_dealer
            INNER JOIN auto_custom_auto_dealer_c ON auto_custom_auto_dealer_c.auto_custo60bd_dealer_ida = auto_dealer.id
            AND auto_custom_auto_dealer_c.deleted = 0
            INNER JOIN auto_customer ON auto_custom_auto_dealer_c.auto_custo0932ustomer_idb = auto_customer.id
            AND auto_customer.deleted = 0
            INNER JOIN auto_vehicluto_customer_c ON auto_vehicluto_customer_c.auto_vehic9275ustomer_ida = auto_customer.id
            AND auto_vehicluto_customer_c.deleted = 0
            INNER JOIN auto_vehicle ON auto_vehicluto_customer_c.auto_vehic831dvehicle_idb = auto_vehicle.id
            AND auto_vehicle.deleted = 0
            INNER JOIN auto_repairauto_vehicle_c ON auto_repairauto_vehicle_c.auto_repai4169vehicle_ida = auto_vehicle.id
            AND auto_repairauto_vehicle_c.deleted = 0
            INNER JOIN auto_repair_order ON auto_repairauto_vehicle_c.auto_repai527cr_order_idb = auto_repair_order.id
            AND auto_repair_order.deleted = 0
            INNER JOIN auto_ro_labrepair_order_c AS aro_lab_pivot ON aro_lab_pivot.auto_ro_laada9r_order_ida = auto_repair_order.id
            INNER JOIN auto_ro_labor AS labor ON aro_lab_pivot.auto_ro_la1301o_labor_idb = labor.id
        WHERE
            1 = 1
            AND COALESCE(auto_repair_order.technician_id, '') != ''
            AND auto_repair_order.service_open_date >= '2021-06-25'
            AND auto_repair_order.service_open_date <= '2021-06-25'
            AND auto_dealer.id = '4e7ef95a-050a-b123-3c3a-4f74ae60fc96'
        GROUP BY
            auto_repair_order.id
    ) as totals

I get the following:

When I try to SUM the repair_order_amount_totals though, the number I’m getting is 2373.36 when it should be 3,675.26

SELECT
    SUM(totals.repair_order_amount_total)
FROM
    (
        SELECT
            auto_repair_order.id,
            auto_repair_order.repair_order_amount_total
        FROM
            auto_dealer
            INNER JOIN auto_custom_auto_dealer_c ON auto_custom_auto_dealer_c.auto_custo60bd_dealer_ida = auto_dealer.id
            AND auto_custom_auto_dealer_c.deleted = 0
            INNER JOIN auto_customer ON auto_custom_auto_dealer_c.auto_custo0932ustomer_idb = auto_customer.id
            AND auto_customer.deleted = 0
            INNER JOIN auto_vehicluto_customer_c ON auto_vehicluto_customer_c.auto_vehic9275ustomer_ida = auto_customer.id
            AND auto_vehicluto_customer_c.deleted = 0
            INNER JOIN auto_vehicle ON auto_vehicluto_customer_c.auto_vehic831dvehicle_idb = auto_vehicle.id
            AND auto_vehicle.deleted = 0
            INNER JOIN auto_repairauto_vehicle_c ON auto_repairauto_vehicle_c.auto_repai4169vehicle_ida = auto_vehicle.id
            AND auto_repairauto_vehicle_c.deleted = 0
            INNER JOIN auto_repair_order ON auto_repairauto_vehicle_c.auto_repai527cr_order_idb = auto_repair_order.id
            AND auto_repair_order.deleted = 0
            INNER JOIN auto_ro_labrepair_order_c AS aro_lab_pivot ON aro_lab_pivot.auto_ro_laada9r_order_ida = auto_repair_order.id
            INNER JOIN auto_ro_labor AS labor ON aro_lab_pivot.auto_ro_la1301o_labor_idb = labor.id
        WHERE
            1 = 1
            AND COALESCE(auto_repair_order.technician_id, '') != ''
            AND auto_repair_order.service_open_date >= '2021-06-25'
            AND auto_repair_order.service_open_date <= '2021-06-25'
            AND auto_dealer.id = '4e7ef95a-050a-b123-3c3a-4f74ae60fc96'
        GROUP BY
            auto_repair_order.id
    ) as totals

Any idea why I might be getting a different value than I’m expecting when attempting to get the SUM?

Thanks!

Answer

To find where the problem is, you should look for suspicious coincidences between what you’re expecting, what you get, and the data.

In this case, 3675.26 – 2373.36 = 1301.90, which is really close the value “1,302.90” in the data. Thanks to user Barmar’s comment, we see that MySQL is parsing the string into a number – but only up to the comma.

The bodge method to fix it would be to remove the comma:

SUM(REPLACE(totals.repair_order_amount_total, ',', ''))

The correct method would be to use the DECIMAL type for all money amounts in the database, and use the UI to format the numbers with commas for display.

Advertisement

Answer