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

I am trying to count unique occurrences of a Client ID in the following code.

我试图在以下代码中计算客户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;

The expression I think needs to be modified is:

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

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

The typical form for the DCount function is (expression, domain, *criteria*). I am pretty sure I need to specify, somehow, that the [Client_ID] should be unique in the criteria argument of the DCount function, but I don't know how. Is this possible?

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

1 个解决方案

#1

If this query doesn't get what you want, please show us with sample data how it differs from what you want.

如果此查询无法获得您想要的内容,请向我们展示样本数据与您想要的不同之处。

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