Getting Around the COUNT DISTINCT Problem in SQLite

Album Cover: Graduation

"Y'all pop the trunk, I pop the hood (Ferrari)."
Kanye West / Good Life

Posted on June 07, 2006 10:06 PM in Web Development
Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

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

Jonathan Orlev on August 12, 2007 at 9:57 AM:

Nice idea, thanks.

Permalink

Steve Forster on December 16, 2007 at 3:27 PM:

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.

Permalink

Sean on September 16, 2010 at 1:49 PM:

Thanks, worked perfectly.

Permalink

Thorben on November 20, 2011 at 7:44 AM:

I'm using sqlite3.7.4.
In this version a SELECT COUNT(DISTINCT foo) FROM bar is working fine.

Permalink

Vernon Cole on February 07, 2013 at 2:46 AM:

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.

Permalink

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.

Name:

Email Address:

Website:

Comments:

Check this box if you hate spam.