计算T-SQL中的唯一结果

时间:2021-09-11 01:41:24

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.

请求帮助。

2 个解决方案

#1


4  

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)

或者您可能需要COUNT(DISTINCT Incident.IncidentID)(取决于您的表结构)

#2


0  

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

从您的PK字段Incident.IncidentID中取出select distinct子句

#1


4  

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)

或者您可能需要COUNT(DISTINCT Incident.IncidentID)(取决于您的表结构)

#2


0  

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

从您的PK字段Incident.IncidentID中取出select distinct子句