GROUPING IS INCONSISTENT error message

[EVANSY]

Hi all,

I am receiving a 'GROUPING IS INCONSISTENT' error message on the following SQL code. Any ideas ?

================================================== =======

SELECT DISTINCT C.C_ACCT_CA_PRFX||
SUBSTR(DIGITS(A.I_ACCT_CA),3,8)||
SUBSTR(DIGITS(A.I_TNT),4,2) ACCOUNT_NUMBER,
B.A_PK_CIS_CR_BAL,
SUM(B.A_KCM_TRAN_AMT),
SUM(D.A_BC_ORIG),
SUM(D.A_VAT_CHRG_ORIG),
SUM(D.A_VAT_PRPD_CHG_OUT)

FROM PRGP.TS083_CUSTOMER A,
PRGP.TR045_BLFIN_COCON C,
PRGP.TS322_POW_KEY_ACT B,
PRGP.TS032_BILL_CHRG D

WHERE A.C_CO = C.C_CO
AND B.I_ACCT_CA = D.I_ACCT_CA
AND B.I_TNT = D.I_TNT
AND B.I_ACCT_CA = A.I_ACCT_CA
AND B.I_TNT = A.I_TNT
AND A.I_ACCT_CA = 00101141
AND A.I_TNT = 03
GROUP BY B.A_KCM_TRAN_AMT,
D.A_BC_ORIG,
D.A_VAT_CHRG_ORIG,
D.A_VAT_PRPD_CHG_OUT

[frozenmist]

Hi EVANSY,
As I see it, you need not group the columns used in the sum().
In that case how will you get the sum? But this is not an error, i think.

I think the problem is that you need to group all other columns that have been mentioned in the select not using aggregate function like sum.
eg: you haven't added the columns which are part of account_number in group by.

Try out these.

Hope you find the solution
Cheers

[EVANSY]

Thanks for the advice but still appear to be getting the problem using the new code below.

Any ideas ?

================================================== =======

SELECT DISTINCT C.C_ACCT_CA_PRFX||
SUBSTR(DIGITS(A.I_ACCT_CA),3,8)||
SUBSTR(DIGITS(A.I_TNT),4,2) ACCOUNT_NUMBER,
B.A_PK_CIS_CR_BAL TOTAL_BALANCE,
SUM(B.A_KCM_TRAN_AMT) TOTAL_KEYCHARGES,
SUM(D.A_BC_ORIG) TOTAL_CHARGES,
SUM(D.A_VAT_CHRG_ORIG) ORIGINAL_VAT,
SUM(D.A_VAT_PRPD_CHG_OUT) TOTAL_VAT

FROM PRGP.TS083_CUSTOMER A,
PRGP.TR045_BLFIN_COCON C,
PRGP.TS322_POW_KEY_ACT B,
PRGP.TS032_BILL_CHRG D

WHERE A.C_CO = C.C_CO
AND B.I_ACCT_CA = D.I_ACCT_CA
AND B.I_TNT = D.I_TNT
AND B.I_ACCT_CA = A.I_ACCT_CA
AND B.I_TNT = A.I_TNT
AND A.I_ACCT_CA = 00101141
AND A.I_TNT = 03
GROUP BY A.I_ACCT_CA,
A.I_TNT,
B.A_PK_CIS_CR_BAL

================================================== =======

[frozenmist]

Hi Evansy,
You also have to have
C.C_ACCT_CA_PRFX
in the group by clause.

Try this also
Cheers