converting int to real in sqlite

Question

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?

Answer 1

If we divide an integer by another integer then the result value will be round off to the nearest integer. So, you can either multiply one of the fields by 1.0 or use CAST to convert one of the fields to float as follows:

select (totalUsers*1.0)/totalBids from

(select (select count(*) from Bids) as totalBids ,

(select count(*) from Users) as totalUsers) A;

1

or

select CAST (totalUsers as FLOAT) / totalBids from

(select (select count(*) from Bids) as totalBids ,

(select count(*) from Users) as totalUsers) A;

1

You can go through this SQL Tutorial by Intellipaat to learn more about type conversions.