How can I count how many duplicates there are for each distinct value in sqlite?

Question

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 1

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;

Answer 2

A quick google gave me this: http://www.mail-archive.com/[email protected]/msg38339.html

select type, count(type) from table group by type;