CODEWITHSUNDEEP
sqlsqlitecastingdivision
vaichidrewar
vaichidrewar

Reputation: 9621

converting int to real in sqlite

Division in sqlite return integer value

sqlite> select totalUsers/totalBids from 
(select (select count(*) from Bids) as totalBids , 
(select count(*) from Users) as totalUsers) A;
1

Can we typecast the result to get the real value of division result?

Upvotes: 114

Views: 96079

Answers (4)

Adam Garner
Adam Garner

Reputation: 1286

In Sqlite the division of an integer by another integer will always round down to the closest integer.

Therefore you need to cast your numerator to a float:

SELECT CAST(field1 AS FLOAT) / field2

Upvotes: 84

Greg
Greg

Reputation: 183

or if you want to update column based on text column:

UPDATE table_with_fields SET real_field=cast(field_with_txt AS real)

Upvotes: 5

mpb
mpb

Reputation: 1482

select cast ( ( select 1 ) as real );

https://www.sqlite.org/lang_expr.html#castexpr

Upvotes: 14

NullUserException
NullUserException

Reputation: 85468

Just multiply one of the numbers by 1.0:

SELECT something*1.0/total FROM somewhere

That will give you floating point division instead of integer division.

Upvotes: 165

Related Questions