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.... :)

Accepted Answer

A quick google gave me this: http://www.mail-archive.com/sqlite-users@sqlite.org/msg38339.html

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

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

Solution

Problem