CAST as INTEGER giving incorrect result

(1) By Akhilesh Airen (airen977) on 2022-12-12 08:03:21 [link] [source]

I have a query where sum(x) is showing 3.0 where as CAST(sum(x) is giving) output of 2. Please see the query:
select  sum(Utilization) as ttl_sum, CAST(sum(Utilization) as integer) as cast_sum from OperationProcess;

Here is DB Export:

PRAGMA foreign_keys = off;
BEGIN TRANSACTION;

-- Table: OperationProcess
CREATE TABLE OperationProcess ( Utilization NUMERIC);

INSERT INTO OperationProcess (Utilization )
                         VALUES (0.05);

INSERT INTO OperationProcess (
                                 Utilization
                             )
                             VALUES (
                                 0.05
                             );

INSERT INTO OperationProcess (
                                 Utilization
                             )
                             VALUES (
                                 0.05
                             );

INSERT INTO OperationProcess (
                                 Utilization
                             )
                             VALUES (
                                 0.049
                             );

INSERT INTO OperationProcess (
                                 Utilization
                             )
                             VALUES (
                                 0.049
                             );

INSERT INTO OperationProcess (
                                 Utilization
                             )
                             VALUES (
                                 0.049
                             );

INSERT INTO OperationProcess (
                                 Utilization
                             )
                             VALUES (
                                 0.9
                             );

INSERT INTO OperationProcess (
                                 Utilization
                             )
                             VALUES (
                                 0.9
                             );

INSERT INTO OperationProcess (
                                 Utilization
                             )
                             VALUES (
                                 0.9
                             );

INSERT INTO OperationProcess (
                                 Utilization
                             )
                             VALUES (
                                 0
                             );

INSERT INTO OperationProcess (
                                 Utilization
                             )
                             VALUES (
                                 0.001
                             );

INSERT INTO OperationProcess (
                                 Utilization
                             )
                             VALUES (
                                 0.001
                             );

INSERT INTO OperationProcess (
                                 Utilization
                             )
                             VALUES (
                                 0.001
                             );


COMMIT TRANSACTION;
PRAGMA foreign_keys = on;

(2.1) By Larry Brasfield (larrybr) on 2022-12-12 08:32:54 edited from 2.0 in reply to 1 [link] [source]

The round() function can be your friend. A more lasting benefit can be had by reading "What Every Computer Scientist Should Know About Floating-Point Arithmetic".

I have to say that you have stumbled over an issue that has plagued beginning programmers from the time computers first had floating point calculation. So it behooves you to look at that doc.

Your education on FP pitfalls can start by running these two querys:
select CAST(round(sum(Utilization)) as integer) from OperationProcess;
select round(sum(Utilization))-sum(Utilization) from OperationProcess;
, then contemplating the results.

(3) By anonymous on 2022-12-12 08:28:26 in reply to 1 [link] [source]

sqlite> .mode quote
sqlite> select  sum(Utilization) as ttl_sum, CAST(sum(Utilization) as integer) as cast_sum from OperationProcess;
2.9999999999999995559,2
sqlite>

Just another case of binary floating point not being able to represent negative powers of 10 exactly.

(4) By Gunter Hick (gunter_hick) on 2022-12-12 08:33:31 in reply to 1 [link] [source]

Your expectations are wrong. None of your decimal fractions have exact representations in double precision floating point. Adding a bunch of approximations may yield something close enough to print as 3.0, but still have an internal representation with an integer part of 2.

(5) By cj (sqlitening) on 2022-12-14 22:06:53 in reply to 1 [source]

Can use integers and divide by number of decimal places
create table t(c integer);
insert into  t values (050);
insert into  t values (050);
insert into  t values (050);
insert into  t values (049);
insert into  t values (049);
insert into  t values (049);
insert into  t values (900);
insert into  t values (900);
insert into  t values (900);
insert into  t values (000);
insert into  t values (001);
insert into  t values (001);
insert into  t values (001);
select format('%.3f',total(c)/1000) from t)