我正在尝试找到购买Brand1和Brand 2的客户以及所花费的美元价值总和。我制作了一个具有所有详细信息的示例数据库。我能够吸引购买两个品牌的客户,但是美元金额应等于品牌1和品牌2中的美元支出。
。这是示例数据库的链接:
https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=8a478b5743099ea5c76acd08e222c0c53
如果我正确理解,则可以使用适当的having
子句使用聚合:
select individual_id, sum(dollar_value_us)
from transaction_detail_mv
group by individual_id
having sum( case when brand_org_code = 'BRAND1' then 1 else 0 end) > 0 and
sum( case when brand_org_code = 'BRAND2' then 1 else 0 end) > 0
使用加冕式子查询
select individual_id, SUM (dollar_value_US) from
transaction_detail_mv t1
where t1.brand_org_code IN ('BRAND1','BRAND2')
AND EXISTS (SELECT 1
FROM TRANSACTION_DETAIL_MV t2 WHERE t1.individual_id=t2.individual_id
and BRAND_ORG_CODE = 'BRAND2')
GROUP BY
individual_id
INDIVIDUAL_ID SUM(DOLLAR_VALUE_US)
1 40
2 22
5 22