代码之家 › 专栏 › 技术社区 › BA82283

如何在Cognos Report Studio中创建每个重复值的不同计数?

cognos-10 sql

BA82283 · 技术社区 · 7 年前

这些是我目前的数据

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

我已经尝试了以下方法,但都没有成功。

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 Number的新列吗?但是我该如何单独计算帐号呢?

1 回复 | 直到 7 年前

Johnsonium 7 年前

我相信你需要的表达方式如下:

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

推荐文章

Community wiki · 我应该写更多的SQL来提高效率,还是应该写更少的SQL来减少bug?

1 年前

Community wiki · SQL语法新手

1 年前

Thomas Leplus · PLS-00306:过程调用中的参数数量或类型错误

1 年前

KateMak · 是否将多行中的多列与唯一id组合?

1 年前

Mateusz UrbaÅski · 在PostgreSQL中按计算经验分组

1 年前

Karuna · SQL中列内的筛选器[重复]

1 年前

Irvan Affandy · 为另一个选择选择声明的键

1 年前

Community wiki · 这个MySQL语句出了什么问题?

1 年前

user22329205 · 如何使用Google BigQuery中的条件,根据特定列值连接列的N行?

1 年前

Community wiki · 优化从同一表中提取的多列的查询

1 年前