Sum / Count Distinct未正确返回

Question

我可以在"ub.Id“列上使用select distinct返回84行。

当我对该列进行distinct或sum条件计数时，返回的值不正确。

我知道有关于这个问题的主题，但我找不到解决方案，我肯定遗漏了什么。它对我来说是直截了当的。返回了84行，但我肯定错过了一个组或其他东西，我已经玩了很多次了。

焦点放在最后3列

SELECT DISTINCT 
    o.Organization_Name,
    ui.DisplayName, 
    ui.NumLogins, 
    COUNT(uw.Title) Assigned,
    SUM(CASE WHEN uw.Status IS NULL THEN 1 ELSE 0 END) NotStarted,
    SUM(CASE WHEN uw.Status = 'incomplete' THEN 1 ELSE 0 END) InProgress,
    SUM(CASE WHEN uw.grade >=80 THEN 1 ELSE 0 END) Completed,
    COUNT(DISTINCT ub.Id) ShouldBe84a,
    SUM(CASE WHEN ub.Id > 0 THEN 1 ELSE 0 END) ShouldBe84b,
    ub.Id
FROM @Organizations o
INNER JOIN @UserInfo ui on ui.OrgId = o.OrgId
LEFT JOIN @UserWorkshop uw on uw.UserId = ui.UserId
LEFT JOIN @UserBehavior ub on ub.UserId = ui.UserId
WHERE username = 'user@email.com'
AND ub.Id IS NOT NULL
GROUP BY o.Organization_Name,
        ui.DisplayName,
        ui.NumLogins, 
        ub.id;

任何帮助都是非常感谢的，

博伊德

**编辑解决感谢帮助，为我指明了正确的方向**

已解析代码：

select o.Organization_Name,ui.DisplayName, ui.NumLogins, 
count(distinct case when uw.Title Is Not Null then uw.Title else null end) Assigned,
count(distinct case when uw.Status Is Null then uw.Title else null end) NotStarted,
count(distinct case when uw.Status = 'incomplete' then uw.Title else null end) InProgress,
count(distinct case when uw.grade >=80 then uw.Grade else null end) Completed,
count(distinct case when ub.CaseStatus = 1 then ub.Id else null end) CasesSupported,
count(distinct case when ub.CaseOwnerId = ui.UserId then ub.Id else null end) CasesOwned
from @Organizations o
inner join @UserInfo ui on ui.OrgId = o.OrgId
left join @UserWorkshop uw on uw.UserId = ui.UserId
left join @UserBehavior ub on ub.UserId = ui.UserId
--left join @UserBehaviorDetails ubd on (ubd.Id=ub.Id and ubd.UserId=ub.UserId)
where username = 'user@email.com'
and ub.Id is not null
group by o.Organization_Name,ui.DisplayName,ui.NumLogins

Answer 1

您计算的是ub.Id的不同值，但也是按同一列分组的。

您需要从列列表和GROUP BY中删除ub.Id，以使聚合正确。在使用GROUP BY时也不需要DISTINCT。

SELECT 
    o.Organization_Name,
    ui.DisplayName, 
    ui.NumLogins, 
    COUNT(uw.Title) Assigned,
    SUM(CASE WHEN uw.Status IS NULL THEN 1 ELSE 0 END) NotStarted,
    SUM(CASE WHEN uw.Status = 'incomplete' THEN 1 ELSE 0 END) InProgress,
    SUM(CASE WHEN uw.grade >=80 THEN 1 ELSE 0 END) Completed,
    COUNT(DISTINCT ub.Id) ShouldBe84a,
    SUM(CASE WHEN ub.Id > 0 THEN 1 ELSE 0 END) ShouldBe84b
FROM @Organizations o
INNER JOIN @UserInfo ui on ui.OrgId = o.OrgId
LEFT JOIN @UserWorkshop uw on uw.UserId = ui.UserId
LEFT JOIN @UserBehavior ub on ub.UserId = ui.UserId
WHERE username = 'user@email.com'
AND ub.Id IS NOT NULL
GROUP BY o.Organization_Name,
        ui.DisplayName,
        ui.NumLogins;