Home > Enterprise >  SUM() function gives incorrect output
SUM() function gives incorrect output

Time:04-24

I have a single table named trust_fund:

trust_fund table

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

CodePudding user response:

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.

  • Related