CODEWITHSUNDEEP
sqlsqlitesql-updatedivision
Martial Portiche
Martial Portiche

Reputation: 13

sqlite: mathematical operation doesn't return numeric, even though the cell is REAL

I have a table with three columns: traffic INTEGER, revtraffic INTEGER, ratio REAL.

When I update the ratio column in the following way:

UPDATE table
SET ratio = revtraffic/traffic

It returns 0.0 in all cells. However, if write:

SET ratio = revtraffic*100/traffic

then it displays the right results but obviously, 100x magnitude too much. What is going on?

Upvotes: 1

Views: 168

Answers (1)

Mureinik
Mureinik

Reputation: 311808

revtraffic and traffic are integers, so dividing them will be done by integer division, which returns only the "whole" part of the division (i.e, the part to the left of the decimal point). This result is then promoted to a real when you assign it to a real.

You can work around this problem by explicitly casting one of the arguments to a real:

UPDATE table SET ratio = (CAST revtraffic AS REAL)/traffic

Upvotes: 1

Related Questions