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?

score 160 · Accepted Answer · answered Nov 29 '11 at 03:53

160

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.

answered Nov 29 '11 at 03:53

NullUserException

5

This does not work for me with SQLite 3.15.. The solution by @AdamGarner does. – Ma0 May 08 '18 at 08:28
2

You really shouldn't use this trick, as it's easy for developers (including yourself) to come back and delete what looks like redundant code. Best practice would be to cast to float as I have suggested below. – Adam Garner May 14 '18 at 09:50
3

This is a hack. The best, most explicit, way to do this is to use CAST. There's also the possibility that this may screw up in specific scenarios. I've seen stuff like this fail when creating a table based on a query. Casting the value fixed that problem. – Mike Feb 25 '20 at 18:50
Anyone working with sqlite is going to know why the *1.0 is there. Why is sqlite so annoying, sigh, can't even divide numbers right :( – chrismarx Sep 06 '22 at 20:30

score 79 · Answer 2 · edited Feb 24 '23 at 14:52

79

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

edited Feb 24 '23 at 14:52

robertspierre

answered Dec 02 '13 at 10:08

Adam Garner

1

So is it better to cast the int manually to float or to multiply with 1.0 (as @NullUserException answered)? – Felix Edelmann May 09 '16 at 20:50
4

Yes, for one simple reason. You are being explicit about what you intend to happen. And not putting in something that relies on other developers knowing why you have multiplied by 1. – Adam Garner May 15 '16 at 16:18
5

@FelixEdelmann Also, if you don't explicitly cast as float, you yourself might even forget why you have that 1.0, and delete it, at some point. It doesn't just apply to other developers. – danuker Aug 18 '17 at 11:07
1

To add to this, as I stated on the accepted answer, we had a field that was being calculated (legitimately) and used in a CREATE TABLE with SELECT and the values were coming back as TEXT values (might have been hitting a NULL or something). Casting was the only thing that fixed it for us. – Mike Feb 25 '20 at 18:52

score 12 · Answer 3 · answered Aug 31 '13 at 17:18

12

select cast ( ( select 1 ) as real );

answered Aug 31 '13 at 17:18

mpb

score 4 · Answer 4 · answered Jul 13 '16 at 10:24

4

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

UPDATE table_with_fields SET real_field=cast(field_with_txt AS real)

answered Jul 13 '16 at 10:24

Greg

4 Answers4