Re: [sqlite] Count(*) help

[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 value is ?  I may have 
> > missed
> > it, but I don't think the answer is in SQL92.  The only solution is not to
> > depend on any particular behaviour.
> 
> The result of any operation involving NULL other than IS NULL and
> IS NOT NULL must be NULL.  

  Not true.  There are formal rules for three-valued logic (or 3VL) that
  every database should follow.  In 3VL, NULL is treated as "unknown",
  but there are cases when an operator can take a NULL argument, but
  still produce a known result.  For example, in SQL "NULL OR 1" is 1
  (true) and "NULL AND 0" is 0 (false).

  Arguments about the semantic details of Relational algebra aside, if
  you treat NULL as "unknown", most of the database operators and
  functions make sense.

  http://en.wikipedia.org/wiki/Three-valued_logic

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[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 solution is not to
> depend on any particular behaviour.

The result of any operation involving NULL other than IS NULL and IS NOT NULL 
must be NULL.  

Note that the coalese(x,y) is just
CASE WHEN x IS NULL THEN y ELSE x END
and coalesce(x,y,z) is
CASE WHEN x IS NULL THEN CASE WHEN y IS NULL THEN z ELSE y END ELSE x 
END
and so forth.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[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 differently 
(it's not), but rather how null is handled:

http://www.sqlite.org/nulls.html

As far as, err, Oracle goes [1]:

"Oracle Database currently treats a character value with a length of zero as 
null. However, this may not continue to be true in future releases, and Oracle 
recommends that you do not treat empty strings the same as nulls."

[1] http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements005.htm
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[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 that SQLite is 
> correct and Oracle is wrong.

Perhaps. In fairness, Oracle predates any 'standard' by a good decade or more.

Also, the practical wisdom of treating the empty string as not null is 
debatable. As demonstrated by this very thread.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[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, then [...]
|b) Otherwise, the result is the null value.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[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 
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 is not 
to depend on any particular behaviour.

Math is hard.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[Black, Michael (IS)]

That's the nice thing about standards...there's so many to choose from.

One man's NULL is another's length=0, is another's "empty" string of ''.
http://en.wikipedia.org/wiki/Null_%28SQL%29

IMHO sqlite3 gets it right and Oracle 11.2.0.1.0 gets it wrong...
Purportedly:
"Null is defined by the ISO SQL standard as different from both an empty string 
or the numerical value 0"

In sqlite3 3.7.13:
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(a);
sqlite> insert into t values(NULL);
sqlite> insert into t values('');
sqlite> select count(a) from t;
1
sqlite> select count(*) from t where a is null;
1
sqlite> select count(*) from t where a ='';
1
sqlite> select count(*) from t where length(a) = 0;
1


SQL> create table t(a varchar(255));
Table created.
SQL> insert into t values(NULL);
1 row created.
SQL> insert into t values('');
1 row created.
SQL> select count(a) from t;
  COUNT(A)
--
 0
SQL> select count(*) from t where a is null;
  COUNT(*)
--
 2
SQL> select count(*) from t where a = '';
  COUNT(*)
--
 0
SQL> select count(*) from t where length(a) = 0;
  COUNT(*)
--
 0



Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


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 though sqlitebrowser declared them
as empty with '' !

On 17 September 2012 15:50, Black, Michael (IS) <michael.bla...@ngc.com>wrote:

> 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='';
> update members set year2012=NULL where year2012='';
>
> Then the counts should be what you want.
>
> 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?
>
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[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 have to supply NULL, without 
quotes.  And there's no way to do that in, for example, a CSV file.  Still you 
now understand the issues and have a working result, so congratulations all 
round.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[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 fine.  The problem in SQL92 is that it doesn't 
define what 'count()' means in terms of values, just objects.  Skipping over a 
number of different uses for count(), the definition wanted here is

"cardinality (of a collection): The number of objects in that
collection. Those objects need not necessarily have distinct
values."

So the question becomes whether NULL is an object or not.  Unfortunately the 
standard says

"A special value, or mark, that is used to
indicate the absence of any data value."

It is clear that NULL is used to indicate the lack of a data value.  
Unfortunately I'm having trouble figuring out whether NULL is an object, or is 
used to indicate the lack of an object.  I suspect that this is what leads to 
different SQL engines implementing count() in different ways.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[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 where year2008='';
> update members set year2009=NULL where year2009='';
> update members set year2010=NULL where year2010='';
> update members set year2011=NULL where year2011='';
> update members set year2012=NULL where year2012='';
>
> Then the counts should be what you want.
>
> 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?
>
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[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='';
update members set year2012=NULL where year2012='';

Then the counts should be what you want.

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?



Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[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
> count("Year2013") from Members used to return 169. In LibreOfiice with the
> data stored in embedded HSQL it returns 169. In LibreOffice connecting to
> sqlite3 it returns 896.
> 
> Any ideas please?

At some point during the conversion from Acess to SQLite, NULL values turned 
into empty strings. See what this query returns:

select sum(Year2012 is null) CountOfNulls, sum(Year2012 = '') CountOfEmpty from 
Members;

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[Black, Michael (IS)]

You have a bad table structure which is helping to cause your problem.

It's pretty obvious that you don't want one column per year, you want want a 
membership table that has member,year, and paid status (or whatever info you're 
keeping for year).

The way you have you have to modify your database and code every 
yearyuckplus your query isn't guaranteed as you've discovered.

And you should have one query for every year in order to guarantee your 
resultsyuck

With the right table structure you never have to modify anything again (until 
year overflows :-).

pragma foreign_keys = on;
create table member(memberid primary key);
create table dues(year integer,status integer,duesid integer, foreign 
key(duesid) references member(memberid));
insert into member values(1);
insert into member values(2);
insert into member values(3);
insert into member values(4);
insert into member values(5);
insert into dues values(2007,1,1);
insert into dues values(2008,1,1);
insert into dues values(2009,1,1);
insert into dues values(2010,1,1);
insert into dues values(2011,1,1);
insert into dues values(2012,1,1);
insert into dues values(2013,1,1);
insert into dues values(2007,NULL,2);
insert into dues values(2008,1,2);
insert into dues values(2009,1,2);
insert into dues values(2010,1,2);
insert into dues values(2011,1,2);
insert into dues values(2012,1,2);
insert into dues values(2013,1,2);
insert into dues values(2007,NULL,3);
insert into dues values(2008,NULL,3);
insert into dues values(2009,1,3);
insert into dues values(2010,1,3);
insert into dues values(2011,1,3);
insert into dues values(2012,1,3);
insert into dues values(2013,1,3);
insert into dues values(2007,NULL,4);
insert into dues values(2008,NULL,4);
insert into dues values(2009,NULL,4);
insert into dues values(2010,1,4);
insert into dues values(2011,1,4);
insert into dues values(2012,1,4);
insert into dues values(2013,1,4);
select year,count(*) from dues group by year order by year;
2007|4
2008|4
2009|4
2010|4
2011|4
2012|4
2013|4
sqlite> select year,count(status) from dues group by year order by year;
2007|1
2008|2
2009|3
2010|4
2011|4
2012|4
2013|4
You can see that count(status) skips the NULL entries.


Or, if you for some reason you don't want to store NULLs (perhaps you have 
multiple possibilities there) you can store 0 for non-paid and do this:
select year,count(status) from dues where status=1 group by year order by year;



Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


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 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 the double-quoted
but it makes no difference.

SELECT COUNT( "Year2007" ), COUNT( "Year2008" ), COUNT( "Year2009" ),
COUNT( "Year2010" ), COUNT( "Year2011" ), COUNT( "Year2012" ), COUNT(
"Year2013" ) FROM "Members"

On 16 September 2012 17:48, Bart Smissaert <bart.smissa...@gmail.com> wrote:

> 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.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 Members used to return 169. In LibreOfiice with
> the
> > data stored in embedded HSQL it returns 169. In LibreOffice connecting to
> > sqlite3 it returns 896.
> >
> > Any ideas please?
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[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 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" ), COUNT(
> "Year2013" ) FROM "Members"
>
> On 16 September 2012 17:48, Bart Smissaert  wrote:
>
>> 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 
>> 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 169. In LibreOfiice with
>> the
>> > data stored in embedded HSQL it returns 169. In LibreOffice connecting to
>> > sqlite3 it returns 896.
>> >
>> > Any ideas please?
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[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( "Year2012" ), COUNT(
"Year2013" ) FROM "Members"

On 16 September 2012 17:48, Bart Smissaert  wrote:

> 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 
> 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 169. In LibreOfiice with
> the
> > data stored in embedded HSQL it returns 169. In LibreOffice connecting to
> > sqlite3 it returns 896.
> >
> > Any ideas please?
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[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 other constant in place of 1] will also return the row count 
(because you have provided a non-null value to be counted for each row).  

In fact, count(expression) is exactly equivalent to sum(1) where expression is 
not null, unless expression includes the keyword distinct.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[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") from Members used to return 169. In LibreOfiice with the
data stored in embedded HSQL it returns 169. In LibreOffice connecting to
sqlite3 it returns 896.

Any ideas please?


Read the documentation?   http://sqlite.org/lang_aggfunc.html

"The count(X) function returns a count of the number of times that /X/ 
is not NULL in a group.
The count(*) function (with no arguments) returns the total number of 
rows in the group. "



HTH,

Gerry

PS  Note that it is count(X) and not count("X")
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[Keith Medcalf]

The Year2012 field is not null, it is an empty string (that is, '', which is 
not NULL).

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf 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 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 the
> data stored in embedded HSQL it returns 169. In LibreOffice connecting to
> sqlite3 it returns 896.
> 
> Any ideas please?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[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 when this table was in Access, select
count("Year2013") from Members used to return 169. In LibreOfiice with the
data stored in embedded HSQL it returns 169. In LibreOffice connecting to
sqlite3 it returns 896.

Any ideas please?


You're probably looking for something like
SELECT COUNT(*) FROM Members WHERE Year2012='Paid'
or maybe even
SELECT Year2012, COUNT(*) FROM Members GROUP BY Year2012



or:
SELECT count(*) FROM Members where Year2012 IS NOT NULL;

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[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 used to return 169. In LibreOfiice with the
> data stored in embedded HSQL it returns 169. In LibreOffice connecting to
> sqlite3 it returns 896.

sqlite> create table Members(Year2012);
sqlite> insert into Members values('Paid');
sqlite> insert into Members values('Comp');
sqlite> insert into Members values(null);
sqlite> select count(*) from Members;
3
sqlite> select count(Year2012) from Members;
2
sqlite> select count("Year2012") from Members;
2
sqlite> select count('Year2012') from Members;
3

What is the exact query you're using in LibreOffice?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[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  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 169. In LibreOfiice with the
> data stored in embedded HSQL it returns 169. In LibreOffice connecting to
> sqlite3 it returns 896.
>
> Any ideas please?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(*) help

[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
> count("Year2013") from Members used to return 169. In LibreOfiice with the
> data stored in embedded HSQL it returns 169. In LibreOffice connecting to
> sqlite3 it returns 896.
> 
> Any ideas please?

You're probably looking for something like
SELECT COUNT(*) FROM Members WHERE Year2012='Paid'
or maybe even
SELECT Year2012, COUNT(*) FROM Members GROUP BY Year2012

All the best
Marcus Ilgner

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Count(*) help

[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 LibreOfiice with the
data stored in embedded HSQL it returns 169. In LibreOffice connecting to
sqlite3 it returns 896.

Any ideas please?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users