将SUM除以Distinct Count并得到错误

Question

我在下面的查询中得到一个ORA-00937(不是按函数分组的单个组(。有什么想法吗？查询只是将SUM除以Count。

SELECT sum(DISTINCT CE.USD_NOTIONAL)/(SELECT COUNT(DISTINCT MARKET_DATE) FROM DATA_EURO_YTD WHERE MARKET_DATE >= TO_DATE('2019-01-01','YYYY-MM-DD'))  
FROM CE
WHERE CE.tradedate >= '01-JAN-2019'
AND CE.exchange IN (SELECT DISTINCT EXCHANGE FROM exchange EX
WHERE EX.FEED IN ('Y'))

Answer 1

将子查询移动到FROM子句：

SELECT SUM(DISTINCT CE.USD_NOTIONAL) / MAX(dey.cntd)
FROM CE CROSS JOIN
(SELECT COUNT(DISTINCT MARKET_DATE) as cntd
FROM DATA_EURO_YTD
WHERE MARKET_DATE >= DATE '2019-01-01'
)   dey
WHERE CE.tradedate >= DATE '2019-01-01'  AND
CE.exchange IN (SELECT EXCHANGE
FROM exchange EX
WHERE EX.FEED IN ('Y')
);

注：

• 将子查询移到FROM子句几乎解决了整个问题。您仍然需要围绕该值进行聚合
• CCD_ 3在CCD_ 4子查询中是冗余的
• 我将日期固定为使用日期文字的DATE前缀