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 year....yuck....plus your query isn't guaranteed as you've discovered. And you should have one query for every year in order to guarantee your results....yuck.... 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