Counting without duplicates

Question

I have a table such as the following

 FK_OrgId , FK_UserId
 3 , 74
 1 , 74
 1 , 74
 3 , 4
 4 , 5
 1 , 5

I'm trying to count FK_OrgId but I have to eliminate duplicates. So, I want a result like

 FK_OrgId, count
 3 , 2
 1 , 2
 4 , 1

Any suggestions?

Answer 1

The key here is to use DISTINCT inside COUNT() so it will only count unique values.

SELECT  FK_OrgId, COUNT(DISTINCT FK_UserId)
FROM    TableName
GROUP   BY FK_OrgId

• SQLFiddle Demo

OUTPUT

╔══════════╦════════════╗
║ FK_ORGID ║ TOTALCOUNT ║
╠══════════╬════════════╣
║        1 ║          2 ║
║        3 ║          2 ║
║        4 ║          1 ║
╚══════════╩════════════╝

Answer 2

you should use distinct key word so as to avoid dupplicates

select  t.FK_OrgId, count(distinct t.FK_UserId)
from    TableName as t
group by t.FK_OrgId