The other day I was trying to get a count of unique records in an SQLite table, and it didn't take long to realize that SQLite doesn't actually support the COUNT(DISTINCT id) method I've grown accustomed to from working with MySQL.
Luckily for me, I was able to find SQL Cheat Sheet, which gives a workaround for databases like SQLite and Access that don't support the COUNT/DISTINCT combo:
SELECT COUNT(customer_state) FROM (SELECT DISTINCT customer_state FROM customers);
The workaround is pretty straightforward. It uses a subselect to get the distinct data, then does a count on that temporary, or what they call "derived," table. They do, however, note the caveat that this approach is typically slower.
Comments
Thanks, this worked a treat :-)
It is worth noting that sqlite v3.4.1 supports count(distinct column).
Apoloogies for the double post, the previous comment had the wrong URL in it for my website.
Thanks, worked perfectly.
I'm using sqlite3.7.4.
In this version a SELECT COUNT(DISTINCT foo) FROM bar is working fine.
Yes, but you still need to do the subselect for distinct combinations of values...
sqlite> select count(distinct state, lga) from nomads_ward_name;
Error: wrong number of arguments to function count()
must be stated as...
sqlite> select count(*) from (select distinct state,lga from nomads_ward_name);
29
........
So thank you for leading me to the correct answer.
Post Comments
If you feel like commenting on the above item, use the form below. Your email address will be used for personal contact reasons only, and will not be shown on this website.
Nice idea, thanks.
Permalink