Not a GROUP BY Expression & aggregate functions

Question

I was wondering why, for this query that I have right here, why I have to use the MAX() aggregate function for the case statements, and not just jump directly into the case statement:

select
bank_id,
tran_branch_code,
acct_sol_id,
acct_sol_name,
transaction_date,
gl_date,
transaction_id,
account_number,
max(case
        when cast(substr(GLSH_Code,0,1) as int) >= 1
                and cast(substr(GLSH_Code,0,1) as int) <= 5
                and trans_type = 'D'
            then (trans_amount)
        --else 0
    end ) Ind_Part_Tran_Dr_RBU,
max(case
        when cast(substr(GLSH_Code,0,1) as int) >= 1
                and cast(substr(GLSH_Code,0,1) as int) <= 5
                and trans_type = 'C'
            then (trans_amount)
        --else 0
    end) Ind_Part_Tran_Cr_RBU,
max(case
        when cast(substr(GLSH_Code,0,1) as int) = 0
                or (cast(substr(GLSH_Code,0,1) as int) >= 6
                and cast(substr(GLSH_Code,0,1) as int) <= 9)
                and trans_type = 'D'
            then (trans_amount)
        --else 0
    end)Ind_Part_Tran_Dr_FCDU,
max(case
        when cast(substr(GLSH_Code,0,1) as int) = 0
                or (cast(substr(GLSH_Code,0,1) as int) >= 6
                and cast(substr(GLSH_Code,0,1) as int) <= 9)
                and trans_type = 'C'
            then (trans_amount)
        --else 0
    end) Ind_Part_Tran_Cr_FCDU,
ccy_alias,
ccy_name,
acct_currency,
tran_currency

from
(
SELECT
    DTD.BANK_ID,
    DTD.SOL_ID Acct_Sol_ID, --Account Sol ID

dtd.br_code Tran_branch_code, -- branch code of the transacting branch
sol.sol_desc Acct_sol_name, -- name/description of SOL

DTD.TRAN_DATE Transaction_Date, --TransactionDate
DTD.GL_DATE GL_Date, --GL Date
TRIM(DTD.TRAN_ID) Transaction_ID, --Transaction ID
DTD.GL_SUB_HEAD_CODE GLSH_Code, --GLSH Code

dtd.tran_amt trans_amount,

GAM.ACCT_CRNCY_CODE Acct_Currency, --Account Currency
DTD.TRAN_CRNCY_CODE Tran_Currency, --Transaction Currency

cnc.crncy_alias_num ccy_alias,
cnc.crncy_name ccy_name,

GAM.FORACID Account_Number, --Account Number
DTD.TRAN_PARTICULAR Transaction_Particulars, --Transaction Particulars
DTD.CRNCY_CODE DTD_CCY,
--GSH.CRNCY_CODE GSH_CCY,
DTD.PART_TRAN_TYPE Transaction_Code,
--'Closing_Balance',
DTD.PSTD_USER_ID PostedBy,
CASE WHEN DTD.REVERSAL_DATE IS NOT NULL
    THEN 'Y' ELSE 'N' END Reversal,
TRIM(DTD.TRAN_ID) REV_ORIG_TRAN_ID,
--OTT.REF_NUM OAP_REF_NUM,
'OAP_SETTLEMENT',
'RATE_CODE',
EAB.EOD_DATE
FROM TBAADM.DTD

LEFT OUTER JOIN TBAADM.GAM ON DTD.ACID = GAM.ACID AND DTD.BANK_ID = GAM.BANK_ID
LEFT OUTER JOIN TBAADM.EAB ON DTD.ACID = EAB.ACID AND DTD.BANK_ID = EAB.BANK_ID AND EAB.EOD_DATE = '24-MAR-2014'

left outer join tbaadm.sol on dtd.sol_id = sol.sol_id and dtd.bank_id = sol.bank_id
left outer join tbaadm.cnc on dtd.tran_crncy_code = cnc.crncy_code

WHERE  DTD.BANK_ID = 'CBC01'
AND GAM.ACCT_OWNERSHIP = 'O'
AND GAM.DEL_FLG != 'Y'
--AND DTD.TRAN_DATE = '14-APR-2014'

AND DTD.TRAN_DATE between '01-APR-2014' and '21-APR-2014'
--and foracid in ('50010112441109','50010161635051')
--and DTD.SOL_ID = '5001'
and GAM.ACCT_CRNCY_CODE = 'USD'

)

group by
    bank_id,
    tran_branch_code,
    acct_sol_id,
    acct_sol_name,
    transaction_date,
    gl_date,
    transaction_id,
    account_number,
    ccy_alias,
    ccy_name,
    Acct_Currency,
    Tran_Currency

Because If I would remove the MAX(), I'd get the "Not a GROUP BY Expression", and Toad points me to the first occurrence of the GLSH_Code. Based from other websites, the cure for this is really adding the MAX() function. I would just like to understand why should I use that particular function, what it exactly does in the query, stuff like that.

EDIT: inserted the rest of the code.

I know for sure what MAX() does, it returns the largest value in an expression. But in this case, I can't seem to figure out exactly what that largest value is that the function is attempting to return.

it seems like this is half part of your query? where is FROM clause? — Ilesh Patel, Apr 23 '14 at 07:37
@IleshPatel edited my original post, it now contains the rest of the code — JamesP, Apr 23 '14 at 07:42
possible duplicate of [ora-00979 not a GROUP BY expression](http://stackoverflow.com/questions/1520608/ora-00979-not-a-group-by-expression) — Ben, Apr 23 '14 at 07:52
@Ben i don't think so. I'm also asking as to WHY I should use such functions, and WHAT do those functions exactly do when added to the query — JamesP, Apr 23 '14 at 08:03

score 1 · Answer 1 · answered Apr 23 '14 at 08:37

The GROUP BY statement declares that all columns returned in the SELECT should be aggregated, but that you want to separate the results by those listed in the GROUP BY.

This means we have to use aggregate functions like MIN, MAX, AVG, SUM, etc. on any column that is NOT listed in the GROUP BY.

It's about telling the SQL engine what the expected results should be when there is more than one option.

In a simple example, we have a table with three columns:

PrimaryId  SubId  RowValue
1          1      1
2          1      2
3          2      4
4          2      8

And an SQL like the following (which is invalid):

 SELECT SubId, RowValue
   FROM SampleTable
  GROUP BY SubId

We know we want the distinct SubId's (because of the GROUP BY), but we don't know what RowValue should be when we aggregate the results.

SubId  RowValue
1      ?
2      ?

We have to be explicit in our query, and indicate what RowValue should be as the results can vary.

If we choose MIN(RowValue) we see:

SubId  RowValue
1      1
2      4

If we choose MAX(RowValue) we see:

SubId  RowValue
1      2
2      8

If we choose SUM(RowValue) we see:

SubId  RowValue
1      3
2      12

Without being explicit there's a high likelihood that the results will be wrong, so our SQL engine of choice protects us from ourselves by enforcing the need for aggregate functions.

score 0 · Answer 2 · answered Apr 23 '14 at 07:48

You have group by clause at the end on all the columns except for Ind_Part_Tran_Dr_RBU, Ind_Part_Tran_Cr_RBU, Ind_Part_Tran_Dr_FCDU, Ind_Part_Tran_Cr_FCDU. In this case oracle wants you to tell what to do with these columns, i.e. based on which function it has to aggregate them for every group it finds.

Not a GROUP BY Expression & aggregate functions

2 Answers2