Re: [sqlite] Count(*) help

2012-09-17 Thread Jay A. Kreibich
On Mon, Sep 17, 2012 at 06:03:12PM -0600, Keith Medcalf scratched on the wall: > > There it is again. What does length(NULL) mean ? Should it be NULL because > > anything derived from "Don't know" is "Don't know" ? Or should it be a non- > > negative integer representing how long the NULL

Re: [sqlite] Count(*) help

2012-09-17 Thread Keith Medcalf
> There it is again. What does length(NULL) mean ? Should it be NULL because > anything derived from "Don't know" is "Don't know" ? Or should it be a non- > negative integer representing how long the NULL value is ? I may have missed > it, but I don't think the answer is in SQL92. The only

Re: [sqlite] Count(*) help

2012-09-17 Thread Petite Abeille
On Sep 17, 2012, at 5:09 PM, Simon Slavin wrote: > I suspect that this is what leads to different SQL engines implementing > count() in different ways. I suspect that you are indeed very confused :D The issue at hand is not whether or not count( * ) is implemented

Re: [sqlite] Count(*) help

2012-09-17 Thread Petite Abeille
On Sep 17, 2012, at 5:23 PM, "Keith Medcalf" wrote: > I don't know if there is a standard for treating empty character strings as > if they were not-present values without first applying one of the builtin > functions designed to deal with NULL. However, my opinion is

Re: [sqlite] Count(*) help

2012-09-17 Thread Clemens Ladisch
Simon Slavin wrote: > What does length(NULL) mean ? When there is no string, there is no string length. > I don't think the answer is in SQL92. | 6.6 | [...] | General Rules | [...] | 4) If a is specified, then |Case: |a) Let S be the . If the value of S is | not the null value,

Re: [sqlite] Count(*) help

2012-09-17 Thread Simon Slavin
On 17 Sep 2012, at 4:30pm, "Black, Michael (IS)" wrote: > sqlite> select count(*) from t where length(a) = 0; > 1 There it is again. What does length(NULL) mean ? Should it be NULL because anything derived from "Don't know" is "Don't know" ? Or should it be a

Re: [sqlite] Count(*) help

2012-09-17 Thread Black, Michael (IS)
___ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of John Clegg [john.cl...@nailsea.net] Sent: Monday, September 17, 2012 10:05 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Count(*) help Yes, that was the problem thanks. Even

Re: [sqlite] Count(*) help

2012-09-17 Thread Simon Slavin
On 17 Sep 2012, at 4:05pm, John Clegg wrote: > Yes, that was the problem thanks. Even though sqlitebrowser declared them > as empty with '' ! No, actually that's your problem. '' is exactly the way to define something as an empty string. To supply a null you would

Re: [sqlite] Count(*) help

2012-09-17 Thread Simon Slavin
On 17 Sep 2012, at 3:50pm, "Black, Michael (IS)" wrote: > I'll note that Oracle doesn't count ''. whereas sqlite3 does. Does the SQL > standard say anything about what a "NULL" value is? And who's correct here > if there is a standard? I believe that the values are

Re: [sqlite] Count(*) help

2012-09-17 Thread John Clegg
Yes, that was the problem thanks. Even though sqlitebrowser declared them as empty with '' ! On 17 September 2012 15:50, Black, Michael (IS) wrote: > Or just fix the existing table: > > update members set year2007=NULL where year2007=''; > update members set year2008=NULL

Re: [sqlite] Count(*) help

2012-09-17 Thread Black, Michael (IS)
Or just fix the existing table: update members set year2007=NULL where year2007=''; update members set year2008=NULL where year2008=''; update members set year2009=NULL where year2009=''; update members set year2010=NULL where year2010=''; update members set year2011=NULL where year2011='';

Re: [sqlite] Count(*) help

2012-09-17 Thread Igor Tandetnik
John Clegg wrote: > I have a table Members with 896 rows and a text field "Year2012". It > contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed > in sqlitebrowser as "empty") > > Back in the olden days when this table was in Access, select >

Re: [sqlite] Count(*) help

2012-09-17 Thread Black, Michael (IS)
org] on behalf of John Clegg [john.cl...@nailsea.net] Sent: Monday, September 17, 2012 1:59 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Count(*) help OK thanks folks. Here is the full query (which is why I can't use WHERE clauses! It has always worked before.. I have remo

Re: [sqlite] Count(*) help

2012-09-17 Thread Bart Smissaert
Hi John, If you zip the file (it sounds it is only small) and mail it to me or to this list and tell what answer you want I (or somebody else) will post the SQL. RBS On Mon, Sep 17, 2012 at 7:59 AM, John Clegg wrote: > OK thanks folks. Here is the full query (which is

Re: [sqlite] Count(*) help

2012-09-17 Thread John Clegg
OK thanks folks. Here is the full query (which is why I can't use WHERE clauses! It has always worked before.. I have removed the double-quoted but it makes no difference. SELECT COUNT( "Year2007" ), COUNT( "Year2008" ), COUNT( "Year2009" ), COUNT( "Year2010" ), COUNT( "Year2011" ), COUNT(

Re: [sqlite] Count(*) help

2012-09-16 Thread Keith Medcalf
> PS Note that it is count(X) and not count("X") Count(X) and Count("X") are identical if X is a column in the table (or join) from which you are counting, and X does not contain embeded spaces, special characters, or other inanities. count(*) counts rows, and count('X') or count(1) [or any

Re: [sqlite] Count(*) help

2012-09-16 Thread Gerry Snyder
On 9/16/2012 9:17 AM, John Clegg wrote: I have a table Members with 896 rows and a text field "Year2012". It contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed in sqlitebrowser as "empty") Back in the olden days when this table was in Access, select count("Year2013")

Re: [sqlite] Count(*) help

2012-09-16 Thread Keith Medcalf
f Of John Clegg > Sent: Sunday, 16 September, 2012 10:18 > To: sqlite-users@sqlite.org > Subject: [sqlite] Count(*) help > > I have a table Members with 896 rows and a text field "Year2012". It > contains "Paid" 156 times, "Comp" 13 times and

Re: [sqlite] Count(*) help

2012-09-16 Thread Luuk
On 16-09-2012 18:21, Marcus Ilgner wrote: On So, 2012-09-16 at 17:17 +0100, John Clegg wrote: I have a table Members with 896 rows and a text field "Year2012". It contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed in sqlitebrowser as "empty") Back in the olden days

Re: [sqlite] Count(*) help

2012-09-16 Thread Clemens Ladisch
John Clegg wrote: > I have a table Members with 896 rows and a text field "Year2012". It > contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed > in sqlitebrowser as "empty") > > Back in the olden days when this table was in Access, select > count("Year2013") from Members

Re: [sqlite] Count(*) help

2012-09-16 Thread Bart Smissaert
Hi John, Funny seeing you here on the SQLite forum. Are these by any chance the ISUG members? Doing a count without a WHERE clause is always likely to give different results with the various SQL implications as far as I know. Why not add a WHERE? RBS On Sun, Sep 16, 2012 at 5:17 PM, John Clegg

Re: [sqlite] Count(*) help

2012-09-16 Thread Marcus Ilgner
On So, 2012-09-16 at 17:17 +0100, John Clegg wrote: > I have a table Members with 896 rows and a text field "Year2012". It > contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed > in sqlitebrowser as "empty") > > Back in the olden days when this table was in Access, select

[sqlite] Count(*) help

2012-09-16 Thread John Clegg
I have a table Members with 896 rows and a text field "Year2012". It contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed in sqlitebrowser as "empty") Back in the olden days when this table was in Access, select count("Year2013") from Members used to return 169. In