sql - 按不同条件在同一列分组两次

Question

我有以下各列的数据：

OFFICER_ID, CLIENT_ID, SECURITY_CODE, POSITION_SIZE 

然后例如每行：

officer1, client100, securityZYX, $100k,
officer2, client124, securityADF, $200k,
officer1, client130, securityARR, $150k,
officer4, client452, securityADF, $200k,
officer2, client124, securityARR, $500k,
officer7, client108, securityZYX, $223k, 
and so on.

如您所见，每个客户都分配了一个官员来买卖证券，但是每个客户可以购买不同的证券。

除了按人员以其客户持有的证券的美元总额对我进行排名（我已经完成）之外，我还需要通过添加客户ID持有的证券总数来创建客户总账户范围，例如，持有的证券总额<$ 1百万美元，介于100万美元至300万美元之间，以及> 300万美元。

我试过了：

SELECT officer_ID, SUM(position_size) as AUM
FROM trades
GROUP BY client_ID
HAVING AUM > 1000000 AND AUM < 3000000; 

我得到了几次出现的所有军官的名单，但没有总数。

我需要一个简单的方法：

Officer_ID | range < 1m | range 1m-3m | range > 3m

官员1，[客户帐户的证券总数少于100万的总数]，[客户帐户的证券总数少于100万至300万的总数]，等等。

拜托，您能指出我正确的方向吗？

更新

我修改了Tim的建议代码，并获得了所需的输出：

SELECT
    OFFICER_ID,
    SUM(CASE WHEN total < 1000000 THEN total END) AS "range < 1m",
    SUM(CASE WHEN total >= 1000000 AND total < 3000000 THEN total END) AS "range 1m-3m",
    SUM(CASE WHEN total >= 3000000 THEN total END) AS "range > 3m"
FROM
(
    SELECT OFFICER_ID, CLIENT_ID, SUM(POSITION_SIZE) AS total
    FROM trades
    GROUP BY OFFICER_ID, CLIENT_ID
) t
GROUP BY
    OFFICER_ID;

太好了，蒂姆，谢谢！

Answer 1

我们可以尝试两次汇总，首先是由高级职员和客户汇总，以获取客户总数，第二次是单独由高级职员，以获取计数：

SELECT
    OFFICER_ID,
    COUNT(CASE WHEN total < 1000000 THEN 1 END) AS "range < 1m",
    COUNT(CASE WHEN total >= 1000000 AND total < 3000000 THEN 1 END) AS "range 1m-3m",
    COUNT(CASE WHEN total >= 3000000 THEN 1 END) AS "range > 3m"
FROM
(
    SELECT OFFICER_ID, CLIENT_ID, SUM(POSITION_SIZE) AS total
    FROM trades
    GROUP BY OFFICER_ID, CLIENT_ID
) t
GROUP BY
    OFFICER_ID;