选择计数(不同)返回不正确的计数

Question

我正在尝试运行一个代码，它将根据年龄类别总结一个客户列表。我有客户机ID和它们的年龄，并且使用一个案例将年龄分组到存储桶中，然后尝试计算每个存储桶中的客户机ID。

关于信息-辅助日期字段只是客户端得到服务的日期，而这只是包含在我的测试中，以使结果保持较小-所以我只关注2019年2月不是评估的服务；而爵士ID是提供服务的位置。

select distinct 
    CASE
        when CD.Age between 0 and 5 then '0-5'
        when CD.Age between 6 and 11 then '6-11'
        when CD.Age between 12 and 14 then '12-14'
        when CD.Age between 15 and 17 then '15-17'
        when CD.Age between 18 and 24 then '18-24'
        when CD.Age between 25 and 54 then '25-54'
        when CD.Age between 55 and 64 then '55-64'
        when CD.Age > 65 then '65+'
        else 'Unknown'
    END AS Age_Group, 
count(distinct AP.Source_Individual_ID) as "Count"
from dm.Assistance_Provided AP, rpt.ClientsDemographics CD
        where CD.Source_Individual_ID = AP.Source_Individual_ID
        and AP.SIR_ID = '1909'
        and AP.Service_Provided <> 'Assessment'
        and year(AP.Assisted_Date) = '2019'
        and month(AP.Assisted_Date) = 2
group by CD.Age

如果我运行不包括计数(Distinct)的代码，我将得到17行数据。这17位客户的年龄都在18-24岁之间，所以所有客户的年龄组都是18-24岁。所以当情况看起来是正确的时候。

但是，如果我使用count(不同的)运行代码，我将得到3行数据。年龄组18-24为3次，3条线的计数分别为2、4和5。我在列表中肯定有17个不同的in，但由于某种原因，当我添加计数(Distinct)时，这个in将减少到总共11个。

我还尝试从select中移除不同的内容，例如

select 
    CASE
        when CD.Age between ....

如果我这样做的话，我会得到5排--仍然是18-24岁，然后分别是2,4,5,4,2 --然后回到我总共的17行。但是我不知道为什么我不只是把一行拿回来:年龄组: 18-24；计数: 17。

我做错了什么？

Answer 1

您需要将case语句放入您的group by中。实际上，由于您的group by CD.Age，它将为每个不同的时代编写一个行。换句话说，如果Age = 12有两行，Age = 13有一行，那么就会得到两行，两行都是以12-14作为年龄组，但是第一行是2，第二行是1。更令人困惑的是，如果每一行只有一行，那么因为两行都是相同的，而且您有distinct子句，所以它将只返回1行，计数为1。

如果您将case语句按以下方式放入组中，那么它将按每个不同的Age_Group进行分组。也就是说，它首先进行案例转换，然后根据它进行分组。

所以试着：

select 
    CASE
        when CD.Age between 0 and 5 then '0-5'
        when CD.Age between 6 and 11 then '6-11'
        when CD.Age between 12 and 14 then '12-14'
        when CD.Age between 15 and 17 then '15-17'
        when CD.Age between 18 and 24 then '18-24'
        when CD.Age between 25 and 54 then '25-54'
        when CD.Age between 55 and 64 then '55-64'
        when CD.Age > 65 then '65+'
        else 'Unknown'
    END AS Age_Group, 
count(distinct AP.Source_Individual_ID) as "Number of Clients Assisted"
from dm.Assistance_Provided AP, rpt.ClientsDemographics CD
        where CD.Source_Individual_ID = AP.Source_Individual_ID
        and AP.SIR_ID = '1909'
        and AP.Service_Provided <> 'Assessment'
        and year(AP.Assisted_Date) = '2019'
        and month(AP.Assisted_Date) = 2
group by CASE
        when CD.Age between 0 and 5 then '0-5'
        when CD.Age between 6 and 11 then '6-11'
        when CD.Age between 12 and 14 then '12-14'
        when CD.Age between 15 and 17 then '15-17'
        when CD.Age between 18 and 24 then '18-24'
        when CD.Age between 25 and 54 then '25-54'
        when CD.Age between 55 and 64 then '55-64'
        when CD.Age > 65 then '65+'
        else 'Unknown'
    END

Answer 2

我怀疑您实际上应该通过Source_Individual进行聚合，然后在任何地方使用条件计数：

SELECT
    CD.Source_Individual_ID,
    COUNT(CASE WHEN CD.Age BETWEEN 0 AND 5 THEN 1 END) AS [0-5],
    COUNT(CASE WHEN CD.Age BETWEEN 6 AND 11 THEN 1 END) AS [6-11],
    COUNT(CASE WHEN CD.Age BETWEEN 12 AND 14 THEN 1 END) AS [12-14],
    COUNT(CASE WHEN CD.Age BETWEEN 15 AND 17 THEN 1 END) AS [15-17],
    COUNT(CASE WHEN CD.Age BETWEEN 18 AND 24 THEN 1 END) AS [18-24],
    COUNT(CASE WHEN CD.Age BETWEEN 25 AND 54 THEN 1 END) AS [25-54],
    COUNT(CASE WHEN CD.Age BETWEEN 55 AND 64 THEN 1 END) AS [55-64]
    COUNT(CASE WHEN CD.Age > 65 THEN 1 END) AS [65+],
    COUNT(*) AS [Number of Clients Assisted]
FROM dm.Assistance_Provided AP 
INNER JOIN rpt.ClientsDemographics CD
    ON CD.Source_Individual_ID = AP.Source_Individual_ID
WHERE
    AP.SIR_ID = '1909' AND
    AP.Service_Provided <> 'Assessment' AND
    YEAR(AP.Assisted_Date) = 2019 AND
    MONTH(AP.Assisted_Date) = 2
GROUP BY
    CD.Source_Individual_ID;

请注意，我已经重写了您的查询，使其使用显式、现代的内部联接，而不是您正在使用的隐式联接。