CODEWITHSUNDEEP
sqlsqliteselectfloating-pointsum
Mihai Grecu
Mihai Grecu

Reputation: 23

SQLite REAL sum vs total

Please help me understand the following results given by SQLite3.

SELECT * 
FROM quantities 
WHERE product_id=1;

1|2|1|15,900000
96|4|1|1,000000
97|5|1|1,000000
98|3|1|1,400000

SELECT TOTAL(quantity) 
FROM quantities 
WHERE product_id=1;

18.0

SELECT SUM(quantity) 
FROM quantities 
WHERE product_id=1;

18.0

The last field quantity from quantities table is REAL.

It seems that the conversion to INT is done before doing the REAL sum. Can I fix this?

Upvotes: 0

Views: 2260

Answers (2)

Adrian Cornwell
Adrian Cornwell

Reputation: 196

I don't understand the problem ... it works fine for me in sqlite. Example below (Are you sure the column is REAL?)

create table if not exists quantities (product_id integer,quantity real);

insert into quantities values (1,   15.9);
insert into quantities values (1,   1);
insert into quantities values (1,   1);
insert into quantities values (1,   1.4);

select      product_id,
            SUM(quantity)
FROM        quantities
GROUP BY    product_id;

and returns...

1   19.3

if I use TOTAL( instead of SUM( it also returns 19.3

A working example can be seen at http://www.datagloop.com/?USERNAME=DATAGLOOP/SO_TRUNC

Upvotes: 0

laalto
laalto

Reputation: 152867

You have commas , in your data in place of decimal point ..

When adding up the column values, sqlite just attempts to interpret the column value as a numeric value and gives up until it finds something that cannot be converted to numeric, using whatever numbers encountered so far:

sqlite> create table a(a);
sqlite> insert into a values('1,1'),('2.2'),('100foo');
sqlite> select sum(a) from a;
103.2

Upvotes: 2

Related Questions