CODEWITHSUNDEEP
sqloracle-database
Chetan Jain
Chetan Jain

Reputation: 11

ORA-00937: not a single-group group function error

I am using 4 tables in my sql all of which are nessesary.but i am getting oracle 00937: not a single-group group function error.pls tell me where i am wrong

 select SUBSTR(d_name,1,8) DNAME, 
           SUBSTR(s_name,1,10) SNAME, 
           bd.bill_year*100+bd.bill_month BILL_MTH,
           COUNT(CASE WHEN bd.TARIFF_CODE IN(50,55,56) THEN  bd.CONSUMER_NUMBER END) IND_CNT,
           NVL(SUM(CASE WHEN bd.TARIFF_CODE IN(50,55,56) THEN  SUM(energy_units_ind+energy_units_com+energy_units_dom) END),0) IND_UNT,
           NVL(SUM(CASE WHEN bd.TARIFF_CODE IN(50,55,56) THEN SUM(TOS_CHARGE) END),0) IND_TOS
    from bill_details bd,billing_inputs bi,consumer_master cm,bult b /* here i am using 4 tables */
    where
          bd.bill_year*100+bd.bill_month=201410 
          and bd.bill_year*100+bd.bill_month=bi.bill_year*100+bi.bill_month
          and cm.consumer_type='C'
          and bd.consumer_number=cm.consumer_number
          and bd.consumer_number=bi.consumer_no
          and b.subdiv=cm.subdivision_code
          and TOS_CHARGE > 0
     GROUP BY SUBSTR(d_name,1,8),SUBSTR(s_name,1,10),bd.bill_year*100+bd.bill_month /*this group by not working */
     order by SUBSTR(d_name,1,8),SUBSTR(s_name,1,10),bd.bill_year*100+bd.bill_month
    /

Upvotes: 1

Views: 380

Answers (1)

Multisync
Multisync

Reputation: 8797

Try to replace this:

 NVL(SUM(CASE WHEN bd.TARIFF_CODE IN(50,55,56) and TOS_CHARGE >0  THEN  SUM(energy_units_ind+energy_units_com+energy_units_dom) END),0) IND_UNT,
 NVL(SUM(CASE WHEN bd.TARIFF_CODE IN(50,55,56) and TOS_CHARGE >0  THEN SUM(TOS_CHARGE) END),0) IND_TOS

with:

 NVL(SUM(CASE WHEN bd.TARIFF_CODE IN(50,55,56) and TOS_CHARGE > 0 THEN energy_units_ind+energy_units_com+energy_units_dom END),0) IND_UNT,
 NVL(SUM(CASE WHEN bd.TARIFF_CODE IN(50,55,56) and TOS_CHARGE > 0 THEN TOS_CHARGE END),0) IND_TOS

You have nested sums in your select list. I suppose that causes the error

Upvotes: 0

Related Questions