Re: [sqlite] Getting number of rows with NULL
I agarre. 😁 Mensaje original De: R Smith Fecha: 5/9/17 4:11 PM (GMT-05:00) A: sqlite-users@mailinglists.sqlite.org Asunto: Re: [sqlite] Getting number of rows with NULL On 2017/09/05 10:00 PM, Stephen Chrzanowski wrote: > On behalf of Cecil, the fault in that logic is that count(*) returns the > number of rows in that table, not whether there is a hole "somewhere: Your > query will either return 1, or, 0. Perhaps this is the opportune moment to learn. Test the theory in SQLite, you will see that Igor is correct (and correct for all versions of SQL, not just -lite). ___ 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
Re: [sqlite] Getting number of rows with NULL
2017-09-05 22:46 GMT+02:00 R Smith : > > > On 2017/09/05 10:13 PM, John McKown wrote: > >> On Tue, Sep 5, 2017 at 3:00 PM, Stephen Chrzanowski >> wrote: >> >> On behalf of Cecil, the fault in that logic is that count(*) returns the >>> number of rows in that table, not whether there is a hole "somewhere: >>> Your >>> query will either return 1, or, 0. >>> >>> >>> I either don't understand you, or I am doing something wrong. I used "a" >> instead of "Last Used" in my example because I'm just plain lazy. >> > > I think Stephen assumed the OP meant that he wanted to know the number of > "holes" (i.e. skipped IDs) in the DB, which I thought was obviously not > what the OP wanted, until the OP mentioned his Tea column cannot have NULL > values, so now I'm slightly lost and Stephen's interpretation seems more > sensible, but then the OP said that Igor's solution is working for him, > which should only work if there are NULL values... so yes, I am very much > confused as to exactly what goes on now. The Tea column cannot have NULL, but "Last Used" can. :-D I want to know how many teas are not used yet. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting number of rows with NULL
On 2017/09/05 10:13 PM, John McKown wrote: On Tue, Sep 5, 2017 at 3:00 PM, Stephen Chrzanowski wrote: On behalf of Cecil, the fault in that logic is that count(*) returns the number of rows in that table, not whether there is a hole "somewhere: Your query will either return 1, or, 0. I either don't understand you, or I am doing something wrong. I used "a" instead of "Last Used" in my example because I'm just plain lazy. I think Stephen assumed the OP meant that he wanted to know the number of "holes" (i.e. skipped IDs) in the DB, which I thought was obviously not what the OP wanted, until the OP mentioned his Tea column cannot have NULL values, so now I'm slightly lost and Stephen's interpretation seems more sensible, but then the OP said that Igor's solution is working for him, which should only work if there are NULL values... so yes, I am very much confused as to exactly what goes on now. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting number of rows with NULL
2017-09-05 22:09 GMT+02:00 Igor Tandetnik : > It's possible I misunderstand what it is the OP is trying to do. But in > any case, the query I show is equivalent to the query the OP has shown > (which, apparently, does what they want), except formulated in a less > roundabout way. Yes, your query is the better one. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting number of rows with NULL
On 9/5/2017 4:05 PM, Igor Tandetnik wrote: On 9/5/2017 4:00 PM, Stephen Chrzanowski wrote: select count(*) from teaInStock where "Last Used" IS NULL; On behalf of Cecil, the fault in that logic is that count(*) returns the number of rows in that table, not whether there is a hole "somewhere: Your query will either return 1, or, 0. What is this "hole" of which you speak? This query can very well report a number other than 1 or 0. count(*) returns the number of rows satisfying the condition in the WHERE clause; in this case, the number of rows having NULL in "Last Used" column. In what way do you believe this fails to satisfy the OP's requirements? It's possible I misunderstand what it is the OP is trying to do. But in any case, the query I show is equivalent to the query the OP has shown (which, apparently, does what they want), except formulated in a less roundabout way. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting number of rows with NULL
On Tue, Sep 5, 2017 at 3:00 PM, Stephen Chrzanowski
wrote:
> On behalf of Cecil, the fault in that logic is that count(*) returns the
> number of rows in that table, not whether there is a hole "somewhere: Your
> query will either return 1, or, 0.
>
>
I either don't understand you, or I am doing something wrong. I used "a"
instead of "Last Used" in my example because I'm just plain lazy.
QLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x (a date,b integer);
sqlite> insert into x values(NULL, 1);
sqlite> insert into x values(NULL, 2);
sqlite> insert into x values('2017-09-01', 2);
sqlite> insert into x values('2017-09-02', 3);
sqlite> insert into x values('2017-09-04', 4);
sqlite> select count(*) from x;
5
sqlite> select count(*) from x where a is NULL;
2
sqlite> select count(*) from x where a is NOT NULL;
3
sqlite>
--
Caution! The OP is an hyperpolysyllabicsesquipedalianist and this email may
cause stress to those with hippopotomonstrosesquipedaliophobia.
Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting number of rows with NULL
On 2017/09/05 10:00 PM, Stephen Chrzanowski wrote: On behalf of Cecil, the fault in that logic is that count(*) returns the number of rows in that table, not whether there is a hole "somewhere: Your query will either return 1, or, 0. Perhaps this is the opportune moment to learn. Test the theory in SQLite, you will see that Igor is correct (and correct for all versions of SQL, not just -lite). ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting number of rows with NULL
As I understand the requirements, he wants to find out how many entries (Not which entries) don't exist between the first ID (Assumed 1) and max ID value. So if he's got 3 rows, but max ID is 5, the result should be 2. But I also suspect you're better in tune with the requirements, since I suspect this had something to do with another thread that I barely was paying attention to. On Tue, Sep 5, 2017 at 4:05 PM, Igor Tandetnik wrote: > On 9/5/2017 4:00 PM, Stephen Chrzanowski wrote: > >> select count(*) from teaInStock where "Last Used" IS NULL; >>> >> >> On behalf of Cecil, the fault in that logic is that count(*) returns the >> number of rows in that table, not whether there is a hole "somewhere: >> Your >> query will either return 1, or, 0. >> > > What is this "hole" of which you speak? This query can very well report a > number other than 1 or 0. > > count(*) returns the number of rows satisfying the condition in the WHERE > clause; in this case, the number of rows having NULL in "Last Used" column. > In what way do you believe this fails to satisfy the OP's requirements? > > -- > Igor Tandetnik > > ___ > 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
Re: [sqlite] Getting number of rows with NULL
2017-09-05 21:55 GMT+02:00 Igor Tandetnik :
> On 9/5/2017 3:45 PM, Cecil Westerhof wrote:
>
>> It is not very important, but I am just curious. I need to know how many
>> records are not yet used. I do that with:
>> SELECT COUNT(*) - COUNT("Last Used") AS "Not Used"
>> FROM teaInStock
>>
>> Is that the correct way, or is there a better way?
>>
>
> Why not be explicit about what you are trying to do?
>
> select count(*) from teaInStock where "Last Used" IS NULL;
You are completely right: that is a much better statement. (Blushing.)
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting number of rows with NULL
On 9/5/2017 4:00 PM, Stephen Chrzanowski wrote: select count(*) from teaInStock where "Last Used" IS NULL; On behalf of Cecil, the fault in that logic is that count(*) returns the number of rows in that table, not whether there is a hole "somewhere: Your query will either return 1, or, 0. What is this "hole" of which you speak? This query can very well report a number other than 1 or 0. count(*) returns the number of rows satisfying the condition in the WHERE clause; in this case, the number of rows having NULL in "Last Used" column. In what way do you believe this fails to satisfy the OP's requirements? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting number of rows with NULL
On behalf of Cecil, the fault in that logic is that count(*) returns the
number of rows in that table, not whether there is a hole "somewhere: Your
query will either return 1, or, 0.
On Tue, Sep 5, 2017 at 3:55 PM, Igor Tandetnik wrote:
> On 9/5/2017 3:45 PM, Cecil Westerhof wrote:
>
>> It is not very important, but I am just curious. I need to know how many
>> records are not yet used. I do that with:
>> SELECT COUNT(*) - COUNT("Last Used") AS "Not Used"
>> FROM teaInStock
>>
>> Is that the correct way, or is there a better way?
>>
>
> Why not be explicit about what you are trying to do?
>
> select count(*) from teaInStock where "Last Used" IS NULL;
>
> --
> Igor Tandetnik
>
>
> ___
> 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
Re: [sqlite] Getting number of rows with NULL
On 9/5/2017 3:45 PM, Cecil Westerhof wrote:
It is not very important, but I am just curious. I need to know how many
records are not yet used. I do that with:
SELECT COUNT(*) - COUNT("Last Used") AS "Not Used"
FROM teaInStock
Is that the correct way, or is there a better way?
Why not be explicit about what you are trying to do?
select count(*) from teaInStock where "Last Used" IS NULL;
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting number of rows with NULL
Untested (Obviously as I don't have your schema)
select max(WheverYourIDFieldIs)-count(WhateverYourIDFieldIs) as "Not Used"
from teaInStock group by WhateverYouIDFieldIs
On Tue, Sep 5, 2017 at 3:45 PM, Cecil Westerhof
wrote:
> It is not very important, but I am just curious. I need to know how many
> records are not yet used. I do that with:
> SELECT COUNT(*) - COUNT("Last Used") AS "Not Used"
> FROM teaInStock
>
> Is that the correct way, or is there a better way?
>
> --
> Cecil Westerhof
> ___
> 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
