Count Unique Results in T-SQL

Question

My query is:

SELECT DISTINCT IncidentStatus.IncidentStatusName, Incident.IncidentID AS Bob
FROM            Incident 
INNER JOIN      IncidentMember 
                 ON Incident.IncidentID = IncidentMember.IncidentId
INNER JOIN      IncidentStatus 
                 ON Incident.IncidentStatusID = IncidentStatus.IncidentStatusID
WHERE        (IncidentMember.MemberId = 6)

And the result is:

IncidentStatusName   Bob
---------------------------
Closed               9267
In Progress          9251
In Progress          9289
New                  7893
Resolved             7750

Required Result is:

IncidentStatusName   Bob
---------------------------
Closed               1
In Progress          2
New                  1
Resolved             1

Help Requested.

Answer 1

take your PK field, Incident.IncidentID, out of the select distinct clause

Answer 2

SELECT IncidentStatus.IncidentStatusName, COUNT(Incident.IncidentID) AS Bob  
  FROM Incident 
INNER JOIN IncidentMember ON Incident.IncidentID = IncidentMember.IncidentId 
INNER JOIN IncidentStatus ON Incident.IncidentStatusID = IncidentStatus.IncidentStatusID 
WHERE (IncidentMember.MemberId = 6)
GROUP BY IncidentStatus.IncidentStatusName

Or maybe you need COUNT(DISTINCT Incident.IncidentID) (depends on your table structure)