How can I do a count distinct in sqlite?

sqlite count sum distinct
Chris Denman picture Chris Denman · Apr 2, 2011 · Viewed 47.7k times · Source

I have a table:

ref,type
1,red
2,red
3,green
4,blue
5,black
6,black

I want the result of a sqlite query to be:

red,2
green,1
blue,1
black,2

I think the hardest thing to do is find a question to match my problem? Then I am sure the answer is around the corner....

:)

Answer

Bhoom Suktitipat picture Bhoom Suktitipat · Jul 31, 2012

My quick google with the terms "count unique values sqlite3" landed me on this post. However, I was trying to count the overall number of unique values, instead of how many duplicates there are for each category.

From Chris's result table above, I just want to know how many unique colors there are. The correct answer here would be four [4].

This can be done using select count(DISTINCT type) from table;

Related questions

SQLite Query in Android to count rows

I'm trying to create a simple Login form, where I compare the login id and password entered at the login screen with that stored in the database. I'm using the following query: final String DATABASE_COMPARE = "select count(*) from users …

Read More
How to get the row count of a query in Android using SQLite?

How do I get the row count of a query in Android using SQLite? It seems my following method does not work. public int getFragmentCountByMixId(int mixId) { int count = 0; SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); Cursor cursor = db.rawQuery( "select count(*) from …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More