Getting number of rows with NULL

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

Getting number of rows with NULL

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

Re: Getting number of rows with NULL

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

Re: Getting number of rows with NULL

Stephen Chrzanowski
In reply to this post by Cecil Westerhof-5
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 <[hidden email]>
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
> [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: Getting number of rows with NULL

Stephen Chrzanowski
In reply to this post by Igor Tandetnik-2
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 <[hidden email]> 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
> [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: Getting number of rows with NULL

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

Re: Getting number of rows with NULL

Cecil Westerhof-5
In reply to this post by Igor Tandetnik-2
2017-09-05 21:55 GMT+02:00 Igor Tandetnik <[hidden email]>:

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

Re: Getting number of rows with NULL

Igor Tandetnik-2
In reply to this post by Igor Tandetnik-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting number of rows with NULL

Stephen Chrzanowski
In reply to this post by Igor Tandetnik-2
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 <[hidden email]> 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
> [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: Getting number of rows with NULL

R Smith
In reply to this post by Stephen Chrzanowski
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting number of rows with NULL

John McKown
In reply to this post by Stephen Chrzanowski
On Tue, Sep 5, 2017 at 3:00 PM, Stephen Chrzanowski <[hidden email]>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting number of rows with NULL

Cecil Westerhof-5
In reply to this post by Igor Tandetnik-2
2017-09-05 22:09 GMT+02:00 Igor Tandetnik <[hidden email]>:

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

Re: Getting number of rows with NULL

R Smith
In reply to this post by John McKown


On 2017/09/05 10:13 PM, John McKown wrote:

> On Tue, Sep 5, 2017 at 3:00 PM, Stephen Chrzanowski <[hidden email]>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting number of rows with NULL

Cecil Westerhof-5
2017-09-05 22:46 GMT+02:00 R Smith <[hidden email]>:

>
>
> On 2017/09/05 10:13 PM, John McKown wrote:
>
>> On Tue, Sep 5, 2017 at 3:00 PM, Stephen Chrzanowski <[hidden email]>
>> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting number of rows with NULL

jose isaias cabrera-3
In reply to this post by Cecil Westerhof-5

I agarre. 😁
-------- Mensaje original --------De: R Smith <[hidden email]> Fecha: 5/9/17  4:11 PM  (GMT-05:00) A: [hidden email] 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
[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