mysql - 如何计算 SQL 中数据的唯一出现次数

Question

我正在尝试计算以下代码中客户端 ID 的唯一出现次数。

TRANSFORM Count(Research.Client_ID) AS CountOfClient_ID
SELECT Research.Treatment, Count(DCount("[Client_ID]","[Letter Status]")) AS [Total Letters Sent]
FROM Research INNER JOIN [Letter Status] ON Research.Client_ID = [Letter Status].Client_ID
GROUP BY Research.Treatment
PIVOT [Letter Status].Letter_Status;

我认为需要修改的表达式是:

Total Letters Sent: Count(DCount("[Client_ID]","[Letter Status]"))

DCount 函数的典型形式是 (expression, domain, *criteria*)。我很确定我需要以某种方式指定 [Client_ID] 在 DCount 函数的 criteria 参数中应该是唯一的，但我不知道如何指定。这可能吗？

Answer 1

如果此查询没有得到您想要的结果，请用示例数据向我们展示它与您想要的结果有何不同。

SELECT
    q.Treatment,
    Count(*) AS [Total Letters Sent]
FROM
    [SELECT DISTINCT
        Treatment,
        Client_ID
    FROM
        Research
    ]. AS q
GROUP BY
    q.Treatment;