0

I want the total unique occurences of NAME from the MS SQL database, but only once for every user. Below is a sample of my data.

USERID      NAME
------------------------
1           name 1
1           name 1
1           name 1
2           name 1
2           name 2
2           name 3
3           name 1
3           name 1
3           name 3
4           name 1

If I use the following query

SELECT COUNT(name) AS total, name FROM TestTable GROUP BY name

I get the following results

7   name 1
1   name 2
2   name 3

But what I want is the result below. The query should ignore 2x name 1 from user 1 and 1x name 1 from user 3.

4   name 1
1   name 2
2   name 3

I have insufficient knowledge of SQL to get this to work, or to know the correct terms to use in the query. So there probably is a duplicate and if so, please point me to it.

VDWWD
  • 35,079
  • 22
  • 62
  • 79

3 Answers3

4

use distinct and count() userid

SELECT COUNT(distinct userid) AS total, name FROM TestTable GROUP BY name
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
4

You can use distinct userid for count()

SELECT COUNT(distinct userid) AS total, name FROM TestTable GROUP BY name
Fahmi
  • 37,315
  • 5
  • 22
  • 31
1

You want DISTINCT inside COUNT() to ignore duplicate counts : 

SELECT COUNT(DISTINCT USERID) AS total, name 
FROM TestTable 
GROUP BY name;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52