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

:)

score 130 · Answer 1 · edited Sep 16 '15 at 19:26

130

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;

edited Sep 16 '15 at 19:26

Cory Roy

5,379
2
28
47

answered Jul 31 '12 at 19:58

Bhoom Suktitipat

2,147
2
17
11

1

And can you provide an example of the best way to use this query. Would it be `long count = DatabaseUtils.longForQuery(db, "select count(DISTINCT type) from table", null);` – ban-geoengineering Jul 04 '14 at 10:49
2

@Bhoom, count(DISTINCT type) is what I looking for today. – Ashwin Kanjariya Aug 11 '17 at 07:59
not what the asker was asking so no up-vote but exactly with I needed, thank you! Also I aliased the column name as `total` like this, `select count(distinct type) as total from table" – ABCD.ca Feb 24 '18 at 01:23
this is not what the question asked – Wes Dec 02 '22 at 20:33

score 71 · Accepted Answer · answered Apr 02 '11 at 10:35

71

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;

answered Apr 02 '11 at 10:35

Håvard

9,900
1
41
46

3

Thanks dude, what did you search for in Google? – Chris Denman Apr 02 '11 at 10:36
8

`sqlite count dupicates`. Easy as that. – Håvard Apr 02 '11 at 10:37

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

2 Answers2

Linked