You need to have at least one of the arguments in floating point so the division operation is carried out in floating point ....
The easiest way to do this is to use total() rather than sum(). sum() always returns an integer if all non-null inputs are integer. total() always returns a floating-point value. If either one or both of the sum() are changed to total() you will get the expected result... SELECT total(pgsize-unused)/sum(pgsize) FROM dbstat WHERE name='Products'; SELECT sum(pgsize-unused)/total(pgsize) FROM dbstat WHERE name='Products'; SELECT total(pgsize-unused)/total(pgsize) FROM dbstat WHERE name='Products'; Will all produce a floating point result. You can also convert to floating point by including a floating point operation that is performed *before* the division such as: SELECT 1.0*sum(pgsize-unused)/sum(pgsize) FROM dbstat WHERE name='Products'; SELECT sum(pgsize-unused)*1.0/sum(pgsize) FROM dbstat WHERE name='Products'; but of course the below will not work since the integer division is done before the conversion to floating point: SELECT sum(pgsize-unused)/sum(pgsize)*1.0 FROM dbstat WHERE name='Products'; unless, of course, you force the order of operations: SELECT sum(pgsize-unused)/(sum(pgsize)*1.0) FROM dbstat WHERE name='Products'; Generally though, just using total() is easier ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert >Sent: Sunday, 3 September, 2017 17:01 >To: General Discussion of SQLite Database >Subject: [sqlite] Example uses of the dbstat virtual table > >It looks this example doesn't work: > >SELECT sum(pgsize-unused)/sum(pgsize) FROM dbstat WHERE name='xyz' > >I thinks it should be something like this: > >SELECT cast(sum(pgsize-unused) as real) / cast(sum(pgsize) as real) >FROM >dbstat WHERE name='xyz' > >RBS >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
