Example uses of the dbstat virtual table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Example uses of the dbstat virtual table

Bart Smissaert
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Example uses of the dbstat virtual table

Simon Slavin-3


On 4 Sep 2017, at 12:00am, Bart Smissaert <[hidden email]> wrote:

> It looks this example doesn't work:
>
> SELECT sum(pgsize-unused)/sum(pgsize) FROM dbstat WHERE name='xyz'

What is the output of

SELECT typeof(pgsize), typeof(unused) FROM dbstat WHERE name='xyz' LIMIT 1

?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Example uses of the dbstat virtual table

Bart Smissaert
Both integer.

RBS

On Mon, Sep 4, 2017 at 12:50 AM, Simon Slavin <[hidden email]> wrote:

>
>
> On 4 Sep 2017, at 12:00am, Bart Smissaert <[hidden email]>
> wrote:
>
> > It looks this example doesn't work:
> >
> > SELECT sum(pgsize-unused)/sum(pgsize) FROM dbstat WHERE name='xyz'
>
> What is the output of
>
> SELECT typeof(pgsize), typeof(unused) FROM dbstat WHERE name='xyz' LIMIT 1
>
> ?
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Example uses of the dbstat virtual table

Keith Medcalf
In reply to this post by Bart Smissaert

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-
>[hidden email]] 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Example uses of the dbstat virtual table

Bart Smissaert
> 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 <[hidden email]> 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-
> >[hidden email]] 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
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users