Counting without duplicates

Question

I have a table like

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

I am trying to read FK_OrgId, but I need to eliminate duplicates. So, I want to get the result, for example

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

Any suggestions?

Answer 1

The key point 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 must use the distinct keyword to avoid duplication

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