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( Year2012 ),
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 john.cl...@nailsea.net wrote:
OK thanks folks. Here is the full query (which is why
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 removed
John Clegg john.cl...@nailsea.net 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
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='';
Yes, that was the problem thanks. Even though sqlitebrowser declared them
as empty with '' !
On 17 September 2012 15:50, Black, Michael (IS) michael.bla...@ngc.comwrote:
Or just fix the existing table:
update members set year2007=NULL where year2007='';
update members set year2008=NULL where
On 17 Sep 2012, at 3:50pm, Black, Michael (IS) michael.bla...@ngc.com 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 fine.
On 17 Sep 2012, at 4:05pm, John Clegg john.cl...@nailsea.net 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 have
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 though sqlitebrowser declared them
as empty with '' !
On 17 September 2012 15:50, Black, Michael
On 17 Sep 2012, at 4:30pm, Black, Michael (IS) michael.bla...@ngc.com 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
non-negative
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 numeric value function
| [...]
| General Rules
| [...]
| 4) If a char length expression is specified, then
|Case:
|a) Let S be the string
On Sep 17, 2012, at 5:23 PM, Keith Medcalf kmedc...@dessus.com 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 that
On Sep 17, 2012, at 5:09 PM, Simon Slavin slav...@bigfraud.org 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
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 solution
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 value is ? I
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 LibreOfiice with
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
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
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 used to return
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 when this
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 the rest are null (confirmed
in sqlitebrowser as empty)
Back in the olden days
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) from Members
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
23 matches
Mail list logo