MS SQL count total items, but only once per user

Question

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.

@PanagiotisKanavos, the amount of 4 should be calculated by ujsing `name 1` only once from each individual user, in this sample data there are 4. — VDWWD, Feb 13 '19 at 10:51

score 4 · Answer 1 · answered Feb 13 '19 at 10:45

4

use distinct and count() userid

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

answered Feb 13 '19 at 10:45

Zaynul Abadin Tuhin

31,407
5
33
63

score 4 · Accepted Answer · answered Feb 13 '19 at 10:45

4

You can use distinct userid for count()

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

answered Feb 13 '19 at 10:45

Fahmi

37,315
5
22
31

1

Thanks. I did not know you could use DISTINCT inside COUNT. – VDWWD Feb 13 '19 at 10:59

Yogesh Sharma · Answer 3 · 2019-02-13T10:57:16.497

1

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

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

edited Feb 13 '19 at 10:57

answered Feb 13 '19 at 10:44

Yogesh Sharma

49,870
5
26
52

MS SQL count total items, but only once per user

3 Answers3