ORA-00937:不是单个组功能问题

Question

我正试图为BRACKTS的每个trans-type找到每个值的交易数量弯曲分析：1-100，101-200

SELECT TO_CHAR(TRANSDATE,'MM-YY') MONTH 
, AGENT_NUMBER 
, COUNT(DISTINCT RECEIVER) NUM_OF_CUST 
, COMMAND_ID 
, BRACKETS 
FROM (
SELECT TRANSDATE 
, AGENT_NUMBER 
, RECEIVER 
, COMMAND_ID 
, CASE 
WHEN COUNT(DISTINCT RECEVIVER) BETWEEN 1 AND 100 THEN '1-100' , 
WHEN COUNT(DISTINCT RECEIVER) BETWEEN 101 AND 200 THEN '101-200' 
ELSE '0' END BRACKETS 
FROM TRANSACTION@ABSDB 
WHERE RESULT_CODE = 'DONE' 
AND TO_CHAR (TRANSDATE,''MM-YY) = '06-21')
GROUP BY  TO_CHAR(TRANSDATE,'MM-YY') 
, AGENT_NUMBER  , COMMAND_ID , BRACKETS 

Answer 1

您需要在COUNT(DISTINCT ...)的内部子查询上有一个GROUP BY(至少有一个拼写错误，可能有两个，请参阅内联注释(：

SELECT MONTH 
,      AGENT_NUMBER 
,      COUNT(DISTINCT RECEIVER) NUM_OF_CUST 
,      COMMAND_ID 
,      BRACKETS 
FROM   (
SELECT TO_CHAR(TRANSDATE, 'MM-YY') AS month
,      AGENT_NUMBER 
,      RECEIVER 
,      COMMAND_ID 
,      CASE 
WHEN COUNT(DISTINCT RECEVIVER) BETWEEN 1 AND 100 THEN '1-100' ,
--                      ^ Is this spelt correctly?
WHEN COUNT(DISTINCT RECEIVER) BETWEEN 101 AND 200 THEN '101-200' 
ELSE '0' END BRACKETS 
FROM   TRANSACTION@ABSDB 
WHERE  RESULT_CODE = 'DONE' 
AND    TO_CHAR(TRANSDATE, 'MM-YY') = '06-21'  -- Also the quotes were wrong here
GROUP BY TO_CHAR(TRANSDATE, 'MM-YY'),
, AGENT_NUMBER 
, RECEIVER 
, COMMAND_ID 
)
GROUP BY  MONTH
, AGENT_NUMBER
, COMMAND_ID
, BRACKETS;