Dividing SUM By A Distinct Count 报错

Dividing SUM By A Distinct Count and getting an error

我在以下查询中收到 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'))

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 子句：

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'))

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 子句几乎可以解决整个问题。您仍然需要围绕该值进行聚合。
• SELECT DISTINCT 在 IN 子查询中是多余的。
• 我修复了日期以使用日期文字的 DATE 前缀。