Not a Group By expression Error

I have a SQL like this;

SELECT B.MUS_K_ISIM AS CUSTOMER_NAME, B.HESAP_NO AS CUSTOMER_NO,
    SUM(B.RISK) AS TOTAL_RISK, 
    (CASE WHEN B.DOVIZ_KOD = 21 THEN 'EUR' WHEN B.DOVIZ_KOD = 2 THEN 'USD' WHEN B.DOVIZ_KOD = 1 THEN 'TL' END) AS CCY,
    ROUND(RISK_CV(:TAR,B.HESAP_NO,B.DOVIZ_KOD),2) AS TOTAL_RISK_EUR,
    ROUND(SUM(MV_EX(B.TEKLIF_NO1,B.TEKLIF_NO2,:TAR)),2) AS RESALE_VALUE_OLD,
    ROUND(SUM(M_V(B.TEKLIF_NO1,B.TEKLIF_NO2,:TAR)),2) AS RESALE_VALUE_NEW,
    NVL(IPOTEK(B.HESAP_NO),0) AS SECURITIES,
    NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 0, 30, B.DOVIZ_KOD),2),0) AS BUCKET1,
    NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 30, 60, B.DOVIZ_KOD),2),0) AS BUCKET2,
    NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 60, 90, B.开发者_开发技巧DOVIZ_KOD),2),0) AS BUCKET3,
    NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 90, 9999, B.DOVIZ_KOD),2),0) AS BUCKET4,
    (CASE WHEN GECIKME_CV(:TAR,B.HESAP_NO, 90, 9999, B.DOVIZ_KOD) > 0 THEN 100 WHEN B.HESAP_NO IN (SELECT HESAP_NO FROM S_TAKIP_MUSTERI) THEN 100 ELSE 0 END) AS PROV,
    (CASE WHEN B.HESAP_NO IN (SELECT HESAP_NO FROM S_TAKIP_MUSTERI) THEN 'E' ELSE 'H' END) AS CAT5,
    ROUND(RISK_CV(:TAR,B.HESAP_NO,B.DOVIZ_KOD)-SUM(M_V(B.TEKLIF_NO1,B.TEKLIF_NO2,:TAR))-NVL(IPOTEK(B.HESAP_NO),0),2) AS NET_PROV,
    (CASE WHEN (RISK_CV(:TAR,B.HESAP_NO,B.DOVIZ_KOD)-SUM(M_V(B.TEKLIF_NO1,B.TEKLIF_NO2,:TAR))-NVL(IPOTEK(B.HESAP_NO),0))<0 OR 
    NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 90, 9999, B.DOVIZ_KOD),2),0) <= 0 THEN 0 
    ELSE ROUND((RISK_CV(:TAR,B.HESAP_NO,B.DOVIZ_KOD)-SUM(M_V(B.TEKLIF_NO1,B.TEKLIF_NO2,:TAR))-NVL(IPOTEK(B.HESAP_NO),0)),2) END) AS CORR_PROV
    FROM S_TEKLIF B
    WHERE NVL(B.RISK,0) > 0 
    --AND (GECIKME_CV(:TAR,B.HESAP_NO, 60, 90, B.DOVIZ_KOD) > 0 OR GECIKME_CV(:TAR,B.HESAP_NO, 90, 9999, B.DOVIZ_KOD) > 0) 
    GROUP BY B.MUS_K_ISIM, B.HESAP_NO, (CASE WHEN B.DOVIZ_KOD = 21 THEN 'EUR' WHEN B.DOVIZ_KOD = 2 THEN 'USD' WHEN B.DOVIZ_KOD = 1 THEN 'TL' END), ROUND(RISK_CV(:TAR,B.HESAP_NO,B.DOVIZ_KOD),2), NVL(IPOTEK(B.HESAP_NO),0), NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 0, 30, B.DOVIZ_KOD),2),0), NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 30, 60, B.DOVIZ_KOD),2),0), NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 60, 90, B.DOVIZ_KOD),2),0), NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 90, 9999, B.DOVIZ_KOD),2),0), (CASE WHEN GECIKME_CV(:TAR,B.HESAP_NO, 90, 9999, B.DOVIZ_KOD) > 0 THEN 100 WHEN B.HESAP_NO IN (SELECT HESAP_NO FROM S_TAKIP_MUSTERI) THEN 100 ELSE 0 END), (CASE WHEN B.HESAP_NO IN (SELECT HESAP_NO FROM S_TAKIP_MUSTERI) THEN 'E' ELSE 'H' END)
    ORDER BY B.MUS_K_ISIM

But i getting this error.

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:

I can't put this code to GROUP BY?

(CASE WHEN GECIKME_CV(:TAR,B.HESAP_NO, 90, 9999, B.DOVIZ_KOD) > 0 THEN 100 WHEN B.HESAP_NO IN (SELECT HESAP_NO FROM S_TAKIP_MUSTERI) THEN 100 ELSE 0 END)

I don't understand the error!!

It is a really annoying feature of the ANSI SQL statndard that any column which is not an aggregate needs to be included in the GROUP BY clause. This is a classic piece of stupid duplication, when it's obvious (I believe MySQL allows us to ignore this part of the standard).

So, I'm afraid what it means is, you need to include the CASE() clause in the GROUP BY clause.

I think the problem is that you reference SUM() at two levels in the query: both at the toppermost level, SUM(B.RISK) but also in the calculations which provide grouping values:

( CASE WHEN ( RISK_CV ( :TAR , B.HESAP_NO , B.DOVIZ_KOD )
                   - SUM ( M_V ( B.TEKLIF_NO1 , B.TEKLIF_NO2 , :TAR ) )
                   - NVL ( IPOTEK ( B.HESAP_NO ) , 0 ) ) < 0

This is pretty tricky to get right.

I think the easiest way to resolve this is to build up the query from nested inline queries. I have re-written your query with three levels. The innermost query - IQ - selects the data, including the functions without any aggregations. The middle query - SQ - calculates the sums. The outermost query applies rounding and other things; this should return the results as per your existing query.

SELECT sq.MUS_K_ISIM AS CUSTOMER_NAME, 
    sq.HESAP_NO AS CUSTOMER_NO,
    sq.TOTAL_RISK,
    (CASE WHEN sq.DOVIZ_KOD = 21 THEN 'EUR' 
        WHEN sq.DOVIZ_KOD = 2 THEN 'USD' 
        WHEN sq.DOVIZ_KOD = 1 THEN 'TL' END) AS CCY,
    ROUND(sq.RISK_EUR,2) AS TOTAL_RISK_EUR,
    ROUND(sq.RESALE_VALUE_OLD,2) AS RESALE_VALUE_OLD,
    ROUND(sq.RESALE_VALUE_OLD,2) AS RESALE_VALUE_NEW,
    sq.SECURITIES,
    NVL(ROUND(sq.BUCKET1,2),0) AS BUCKET1,
    NVL(ROUND(sq.BUCKET2,2),0) AS BUCKET2,
    NVL(ROUND(sq.BUCKET3,2),0) AS BUCKET3,
    NVL(ROUND(sq.BUCKET4,2),0) AS BUCKET4,
    (CASE WHEN sq.BUCKET4,2) > 0 THEN 100 
            WHEN sq.CAT5 = 'H' THEN 100 
            ELSE 0 END) AS PROV,
    sq.CAT5,
    sq.NET_PROV,
    (CASE WHEN sq.NET_PROV <0 
    OR
    sq.RISK_EUR <= 0 THEN 0
    ELSE ROUND(sq.NET_PROV,2) END) AS CORR_PROV
from (
    select 
            iq.MUS_K_ISIM , 
            iq.HESAP_NO ,
            iq.DOVIZ_KOD, 
            iq.RISK_EUR,
            iq.SECURITIES,
            iq.BUCKET1,
            iq.BUCKET2,
            iq.BUCKET3,
            iq.BUCKET4,
            iq.CAT5
            sum(iq.RISK) as TOTAL_RISK,
            sum(iq.RESALE_VALUE_OLD) as RESALE_VALUE_OLD,
            sum(iq.RESALE_VALUE_NEW) as RESALE_VALUE_NEW,
            iq.RISK_EUR - sum(iq.RESALE_VALUE_NEW) - iq.SECURITIES) AS NET_PROV
        from (
            SELECT B.MUS_K_ISIM , 
                   B.HESAP_NO ,
                   B.RISK,
                   B.DOVIZ_KOD, 
                   RISK_CV(:TAR,B.HESAP_NO,B.DOVIZ_KOD) AS RISK_EUR,
                   MV_EX(B.TEKLIF_NO1,B.TEKLIF_NO2,:TAR) AS RESALE_VALUE_OLD,
                   M_V(B.TEKLIF_NO1,B.TEKLIF_NO2,:TAR) AS RESALE_VALUE_NEW,
                   NVL(IPOTEK(B.HESAP_NO),0) AS SECURITIES,
                   GECIKME_CV(:TAR,B.HESAP_NO, 0, 30, B.DOVIZ_KOD) AS BUCKET1,
                   GECIKME_CV(:TAR,B.HESAP_NO, 30, 60, B.DOVIZ_KOD) AS BUCKET2,
                   GECIKME_CV(:TAR,B.HESAP_NO, 60, 90, B.DOVIZ_KOD) AS BUCKET3,
                   GECIKME_CV(:TAR,B.HESAP_NO, 90, 9999, B.DOVIZ_KOD) AS BUCKET4,
                   nvl2(M.HESAP_NO, 'E', 'H') AS CAT5
            from     
                FROM S_TEKLIF B
                    left outer join S_TAKIP_MUSTERI on (B.HESAP_NO = M.HESAP_NO)
                WHERE NVL(B.RISK,0) > 0
            ) iq        
        group by     
                iq.MUS_K_ISIM , 
                iq.HESAP_NO ,
                iq.DOVIZ_KOD, 
                iq.RISK_EUR,
                iq.SECURITIES,
                iq.BUCKET1,
                iq.BUCKET2,
                iq.BUCKET3,
                iq.BUCKET4,
                iq.CAT5
        )sq
ORDER BY sq.MUS_K_ISIM
/

Obviously, this may not compile - I haven't got your database schema to check against. Also the logic may be wrong; I have had to make assumptions about your business logic. However, I do think this is easier to understand and will be easier to get debug.

 , ROUND ( RISK_CV ( :TAR , B.HESAP_NO , B.DOVIZ_KOD )
         - SUM ( M_V ( B.TEKLIF_NO1 , B.TEKLIF_NO2 , :TAR ) )
  - NVL ( IPOTEK ( B.HESAP_NO ) , 0 ) , 2 )         

This is not an aggregate function. Only the SUM part is there. Use Analytic functions.

First of all, you might want to post a complete example next time - when trying to run your query, I get errors for at least 4 undefined functions and 2 not existing tables (S_TEKLIF, GECIKME_CV, ipotek, risk_cv, m_v, mv_ex - I gave up after that).

Second: try to split this up into two queries, something like

select mus_k_isim, hesap_no, my_key, ... from 
(select mus_k_isim, b.hesap_no, 
  (CASE WHEN B.DOVIZ_KOD = 21 THEN 'EUR' WHEN B.DOVIZ_KOD = 2 THEN 'USD' WHEN B.DOVIZ_KOD = 1 THEN 'TL' END), ROUND(RISK_CV(:TAR,B.HESAP_NO,B.DOVIZ_KOD),2), NVL(IPOTEK(B.HESAP_NO),0), NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 0, 30, B.DOVIZ_KOD),2),0), NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 30, 60, B.DOVIZ_KOD),2),0), NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 60, 90, B.DOVIZ_KOD),2),0), NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 90, 9999, B.DOVIZ_KOD),2),0), (CASE WHEN GECIKME_CV(:TAR,B.HESAP_NO, 90, 9999, B.DOVIZ_KOD) > 0 THEN 100 WHEN B.HESAP_NO IN (SELECT HESAP_NO FROM S_TAKIP_MUSTERI) THEN 100 ELSE 0 END), (CASE WHEN B.HESAP_NO IN (SELECT HESAP_NO FROM S_TAKIP_MUSTERI) THEN 'E' ELSE 'H' END) my_key
from ...
) group by mus_k_isim, hesap_no, my_key

Your case references a select statement - and you can't have a select statement in a group by clause.