> 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.
Thanks, had forgotten about that one. RBS On Mon, Sep 4, 2017 at 1:32 AM, Keith Medcalf <kmedc...@dessus.com> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
