Search code examples
sqlitecountsumdistinct

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

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

:)

Solution

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

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