SUM() function gives incorrect output

Question

I have a single table named trust_fund:

My Query is: $sql = "SELECT SUM(available_balance) AS total_fund FROM trust_funds WHERE type = 'Admin'";

at first, it displays 100000000 which is correct, but when I update the table (transfered 500 from Admin to Agent), Admin available_balance become 99,999,504 and Agent becomes 500.

There is an extra 4 in Admin Balance. I tried transfering the 500 back to Admin Account and Admin Account becomes 100000008.

The Output of the Query

I am confused because the tables shows correct values, 100000000 and 0, but running the query will give 100000004.

Please help

Table Definition

Column	Type
id	INT(AI)
uid	Text
available_balance	Float
wallet_address	Text
type	Text

Please post your table definition, (as text, please, not a screen shot) — Tangentially Perpendicular, Apr 23 '22 at 09:31

score 0 · Accepted Answer · answered Apr 23 '22 at 10:36

0

Your problem arises from your use of the FLOAT data type for currency.

FLOAT is a poor choice for a currency column because it attracts errors from representation and rounding (See Is floating point math broken?)

Use INT (or BIGINT) if you're working with integer values. Alternatively, use DECIMAL to store and work with exact decimal values.

answered Apr 23 '22 at 10:36

Tangentially Perpendicular

5,012
4
11
29

Thanks a lot Sir.. It works... I changed the Float to Decimal, run the Query and there, the rows show 100000008... Thank you... – Mang Isko Albularyo Apr 23 '22 at 10:54
Thanks a lot Sir.. It works... I changed the Float to Decimal, run the Query and there, the rows show 100000008... Thank you... – Mang Isko Albularyo Apr 23 '22 at 10:54

SUM() function gives incorrect output

1 Answers1