这些是我目前的数据
ACCOUNT NUMBER PRODUCT STATUS RANK DESIGN
1 530033 Wealth Services Closed 1 Manual
2 530033 Wealth Services Closed 2 Manual
3 530033 Wealth Services Closed 3 Manual
4 530033 Wealth Services Closed 4 Manual
5 534656 Initial Escrow Open 1 Manual
6 534656 Initial Escrow Open 2 Manual
7 535161 Markets Integrity Closed 1 Manual
8 538379 Prepaid Cards Closed 1 Manual
9 538379 Prepaid Cards Closed 2 Manual
10 538379 Prepaid Cards Closed 3 Manual
11 538379 Prepaid Cards Closed 4 Manual
12 538915 Uploaded Cards Open 1 Manual
13 538915 Uploaded Cards Open 2 Manual
14 538915 Uploaded Cards Open 3 Manual
我想创建一个列来唯一计算每个重复的帐户编号。比如账号530033出现了四次，我想每个530033都算一个，同理，另一个账号534656出现了两次就算一个。
我已经尝试了以下但这些都不起作用。
count(distinct(Account Number))
count(distinct(Account Number) for Account Number)
聚合列等于 TOTAL、COUNT 或 AUTOMATIC。
我将使用这些数据创建一个交叉表。我的交叉表给了我 14 的总计数，因为它计算了所有重复的帐号。因为所有帐号都有十四行。但实际上只有 5 个帐号（530033、534656、535161、538379 和 53891）
TOTAL Open Closed
Wealth Services 4 4
Initial Escrow 2 2
Markets Integrity 1 1
Prepaid Cards 4 4
Uploaded Cards 3 3
TOTAL 14
理想情况下应该是这样。
TOTAL Open Closed
Wealth Services 1 1
Initial Escrow 1 1
Markets Integrity 1 1
Prepaid Cards 1 1
Uploaded Cards 1 1
TOTAL 5
我应该创建一个名为 Unique count of account numbers 的新列吗？但是我如何单独计算帐号？
我是否必须创建另一个具有唯一帐号的表然后内部连接两个表？
我相信你需要的表达如下：
全部的
COUNT(DISTINCT [ACCOUNT NUMBER] for [PRODUCT])
打开
CASE [STATUS]
WHEN 'Open' THEN COUNT(DISTINCT [ACCOUNT NUMBER] for [PRODUCT],[STATUS])
ELSE NULL
END
关闭
CASE [STATUS]
WHEN 'Closed' THEN COUNT(DISTINCT [ACCOUNT NUMBER] for [PRODUCT],[STATUS])
ELSE NULL
END
确保将每个数据项的聚合函数设置为“已计算”，因为我们是手动指定聚合和汇总的。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句