Oracle Sql Query : Error Query on SUM()

Question

i want sum() some column. but does'nt work. it's error ORA-00937 please help me.

Query

SELECT MB.COUNTRY_CODE, /* Error ORA-00937 This line */
       MB.OPERATOR_NAME AS COUNTRY_NAME,
       C.PMN_CODE AS OPERATOR_NAME,
       (C.RANG_START / 1024) AS RANG_START,
       (C.RANG_END / 1024) AS RANG_END,
       COUNT(*) AS TRANSACTION_BY_OPER,
       '0' AS TERMINATE_CHARGE,
       SUM(C.RATE * (COUNT(*))) AS TRANSIT_CHARGE       
  FROM IR_V_IMM_RATE_BY_CDR C 
  LEFT JOIN IR_PMN_MASTER_BROKER MB 
    ON C.COMPANY_CODE = MB.COMPANY_CODE 
   AND C.PMN_DOMAIN = MB.PMN_CODE
  LEFT JOIN IR_PMN_INTER_SERVICE S 
    ON C.PMN_DOMAIN = S.PMN_CODE 
   AND C.COMPANY_CODE = S.COMPANY_CODE
  WHERE C.SUB_EVENT_TYPE_ID = '2'
   AND C.MODIFIER = 'P2P'
   AND S.INTER_SERVICE = 'MMS'
   AND C.COMPANY_CODE = 'AIS'
   AND C.EVENT_TYPE_ID = '602'
   AND C.PMN_DOMAIN = 'AICEN'
   AND TO_CHAR(C.CUTOFF_DATE, 'MM/YYYY') = '04/2015'
  GROUP BY MB.COUNTRY_CODE, MB.OPERATOR_NAME, C.PMN_CODE, C.RANG_START,  
           C.RANG_END, C.RATE

if delete 3 line. it's work

COUNT(*) AS TRANSACTION_BY_OPER,
'0' AS TERMINATE_CHARGE,
SUM(C.RATE * (COUNT(*))) AS TRANSIT_CHARGE
....
...
/* Fix If delete 3 line */
GROUP BY MB.COUNTRY_CODE, MB.OPERATOR_NAME,C.PMN_CODE,C.RANG_START,C.RANG_END

now, i want add 3 line in this query. but Error ORA-00937

thanks in advance. ;)

try including TERMINATE_CHARGE, in group by and check..include all fields in group by which is not inside an aggregate function at select statement — Sachu, May 19 '15 at 14:11
Change the name to Operator_Name2 and see if it works `C.PMN_CODE AS OPERATOR_NAME,` I think it may be conflicting with `MB.Operator_name` in group by — xQbert, May 19 '15 at 14:12
Related: http://stackoverflow.com/questions/268429/group-by-alias-oracle — Politank-Z, May 19 '15 at 14:14

score 0 · Answer 1 · answered May 19 '15 at 14:15

SELECT MB.COUNTRY_CODE, /* Error ORA-00937 This line */
       MB.OPERATOR_NAME AS COUNTRY_NAME,
       C.PMN_CODE AS OPERATOR_NAME,
       (C.RANG_START / 1024) AS RANG_START,
       (C.RANG_END / 1024) AS RANG_END,
       COUNT(*) AS TRANSACTION_BY_OPER,
       '0' AS TERMINATE_CHARGE,
       SUM(C.RATE * (COUNT(*))) AS TRANSIT_CHARGE       
  FROM IR_V_IMM_RATE_BY_CDR C 
  LEFT JOIN IR_PMN_MASTER_BROKER MB 
    ON C.COMPANY_CODE = MB.COMPANY_CODE 
   AND C.PMN_DOMAIN = MB.PMN_CODE
  LEFT JOIN IR_PMN_INTER_SERVICE S 
    ON C.PMN_DOMAIN = S.PMN_CODE 
   AND C.COMPANY_CODE = S.COMPANY_CODE
  WHERE C.SUB_EVENT_TYPE_ID = '2'
   AND C.MODIFIER = 'P2P'
   AND S.INTER_SERVICE = 'MMS'
   AND C.COMPANY_CODE = 'AIS'
   AND C.EVENT_TYPE_ID = '602'
   AND C.PMN_DOMAIN = 'AICEN'
   AND TO_CHAR(C.CUTOFF_DATE, 'MM/YYYY') = '04/2015'
  GROUP BY MB.COUNTRY_CODE, MB.OPERATOR_NAME, C.PMN_CODE, C.RANG_START,  
           C.RANG_END,'0'

You are including '0' and it is not included in group by and it is neither in aggregate function. Also remove C.Rate from group by because it is using inside aggregate function Sum.try this

score 0 · Accepted Answer · answered May 19 '15 at 14:18

0

You have:

  SUM(C.RATE * (COUNT(*))) AS TRANSIT_CHARGE

Nesting aggregation functions (generally) doesn't work. You probably just want:

  SUM(C.RATE) AS TRANSIT_CHARGE

This adds up all the rates on the rows generated before the group by.

answered May 19 '15 at 14:18

Gordon Linoff

1,242,037
58
646
786

Thanks! I try delete count(). it's work. but result calculate number not required. thanks u so much. ;D – nettoon493 May 19 '15 at 15:58

Oracle Sql Query : Error Query on SUM()

2 Answers2