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
