CODEWITHSUNDEEP
sqlite
Nikki Locke
Nikki Locke

Reputation: 2951

Why can SQLite not add up DECIMAL values?

    sqlite> create table test (Qty DECIMAL(10,2));
    sqlite> insert into test values(1059.79);
    sqlite> insert into test values(-1050);
    sqlite> insert into test values(83.58);
    sqlite> insert into test values(130.51);
    sqlite> insert into test values(-5);
    sqlite> insert into test values(-136.68);
    sqlite> insert into test values(-2.6);
    sqlite> insert into test values(-75);
    sqlite> insert into test values(-4.6);
    sqlite> select sum(Qty) FROM TEST;
    -6.21724893790088e-14

Given that I explicitly specified DECIMAL(10,2) as the field type, why is the sum not 0?

Upvotes: 3

Views: 2658

Answers (3)

Nikki Locke
Nikki Locke

Reputation: 2951

Rather than messing about, and making my sql incompatible with the other databases I support, I merely wrote a user-defined replacement for the SQLite SUM function, like so:

[SqliteFunctionAttribute(Name = "SUM", Arguments = 1, FuncType = FunctionType.Aggregate)]
    class SqliteSum : SqliteFunction {
        public override void Step(object[] args, int stepNumber, ref object contextData) {
            if (args.Length < 1 || args[0] == null || args[0] == DBNull.Value)
                return;
            try {
                decimal d = Math.Round(Convert.ToDecimal(args[0]), 4);
                if (contextData != null) d += (Decimal)contextData;
                contextData = d;
            } catch (Exception ex) {
                WebServer.Log("Exception: {0}", ex);
            }
        }

        public override object Final(object contextData) {
            return contextData;
        }
    }

Upvotes: 1

Ahmad Ebrahimi
Ahmad Ebrahimi

Reputation: 235

SQLite does not have decimal type : 

NULL
INTEGER
REAL
TEXT
BLOB

So maybe it is converted to Text , then suming happens. SQLite is powerfull enough to work with, and can rely on for calculations and math, too.

Upvotes: 0

Colonel Thirty Two
Colonel Thirty Two

Reputation: 26589

SQLite does not have a DECIMAL type. Math is performed using 64-bit floating point values.

See SQLite3 data types

Upvotes: 2

Related Questions