How to Convert Integer Results to Real Values in SQLite Queries?

Converting Integers to Real Values in SQLite

SQLite operations involving numeric data often return integer results by default. However, in scenarios where you require real values, it becomes essential to convert the integers to decimals.

Consider the following example:

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

Here, the division of integers returns an integer result of 1. To obtain the real value of the division, we can utilize typecasting techniques.

Solution: Multiply by 1.0

To convert the integer result to a real value, we can multiply one of the numbers in the division by 1.0. This simple operation forces floating-point division, resulting in a real value.

SELECT something*1.0/total FROM somewhere

Applying this technique to our previous example:

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

Now, the result is a real value representing the actual calculation of total users divided by total bids.

The above is the detailed content of How to Convert Integer Results to Real Values in SQLite Queries?. For more information, please follow other related articles on the PHP Chinese website!