Hello, I am new to sqlite and I wish to get the count of unique entries
in a particular field. The table is created as follows:
create table tablename
(
field_id char(10) not null ,
fieldname char(100)
);
create unique index findex on tablename
(field_id);
I get an error with the query:
select (distinct fieldname) from tablename;
but this gives me the wrong answer: select distinct (fieldname) from tablename;
Sorry for such a novice question and thank you for any help.
The various solutions I've seen so far on this list have used either sub-queries or what looks like non-standard SQL ('count' with an argument). I have a different solution which should be simpler and faster to implement, plus a lot more database-portable. This sort of thing is exactly what GROUP BY is for.
If you want to know all distinct values and their counts, use this:
SELECT fieldname, count(*) FROM tablename GROUP BY fieldname
If you only want to know how many instances of one value there are, use this:
SELECT count(*) FROM tablename WHERE fieldname = 'foo'
Now don't get me wrong, sub-queries are a very valuable feature; I just don't believe they should be used when an efficient group by or join can be used instead. Best tool for the job and all that.
-- Darren Duncan
