CODEWITHSUNDEEP
sqlsqlitedistinctdistinct-values
joe
joe

Reputation: 3

SQL - Counting unique rows based on a separate field

I am trying to count unique values on a per user basis and end up with a combined count. They may exist more than once per user, but should only be counted once per user.

Example:

user   value
1      AAA
1      AAA
1      BBB
1      CCC
2      AAA
2      CCC
2      CCC
3      AAA
3      BBB
3      BBB
3      BBB

Expected result with count:

AAA  3
BBB  2
CCC  2

So values should only be counted once per user, no matter how many times they are present.

I have gotten as far as counting the total number of values with this:

SELECT value, COUNT(value) FROM table GROUP BY value")

But this counts all instances of each value, I cannot work out how to count only the unique values per user and the combine. Hope this makes sense! Many thanks!

Upvotes: 0

Views: 60

Answers (1)

Grayson
Grayson

Reputation: 601

Try this:

SELECT value, COUNT(distinct user) FROM table GROUP BY value

Upvotes: 4

Related Questions