Aggregate in case statement with "not a GROUP BY expression" error

Question

Why do I get "not a GROUP BY expression" error when I include below case statement in my query even though the case statement below uses an aggregate which is not allowed in GROUP BY? When I include the case statement in the GROUP BY I, of course, get "group function is not allowed here" error, but when I remove it from GROUP BY I get "not a GROUP BY expression" error again.

Could anyone offer an explanation, please? Thank you in advance!

        ,case
                when
                        round(sum(i.INVOICE_AMOUNT))  > 50000 and 
                        (cont_all.CONTRACT_EFFECTIVE_DATE > '&end_date'
                        or cont_all.CONTRACT_EXPIRATION_DATE <= '&end_date'
                        or cont_all.CONTRACT_EFFECTIVE_DATE is null) 
                        then 'Over 50k and no Contract'
                else 'Contract or No Contract with spend below 50k'
                end as "Contract Needed?"

Here is the full query as suggested by mathguy:

define end_date = '30-SEP-20'

    select
             v.SEGMENT1 "Vendor Number"
            ,v.VENDOR_NAME "Vendor Name"
            ,s.VENDOR_SITE_CODE "Site Name"
            ,case
                    when s.ORG_ID in ('285',    '296',  '294',  '327',  '304',  '297',  '295',  '312',  '313',  '315',  '319',  '322',  '321',  '306') then 'Fleet'
                    else 'Non-Fleet'
                    end as "Fleet / Non-Fleet"
            ,case
                    when s.ORG_ID in ('288','325','285') then 'Belgium'
                    when s.ORG_ID in ('301','296','302','294') then 'Germany'
                    when s.ORG_ID in ('327','305','304') then 'Spain'
                    when s.ORG_ID in ('298','303','297','299','300','295') then 'France'
                    when s.ORG_ID in ('309','307') then 'Ireland'
                    when s.ORG_ID in ('316','312','314','317','313') then 'Italy'
                    when s.ORG_ID in ('318','315') then 'Luxembourg'
                    when s.ORG_ID in ('318','315') then 'Monaco'
                    when s.ORG_ID in ('322','323','324','326','321') then 'Netherlands'
                    when s.ORG_ID in ('308','310','311','306') then 'United Kingdom'
                    else 'Error'
                    end as "Country"
            ,o.NAME "Operating Unit"
            ,to_number(round(sum(i.INVOICE_AMOUNT))) "Spend last 12 months"
            ,case
                    when round(sum(i.INVOICE_AMOUNT))  > 50000 then 'Over 50,000'
                    when round(sum(i.INVOICE_AMOUNT))  <= 50000 then 'Below 50,000'
                    when round(sum(i.INVOICE_AMOUNT))  = 0 then 'No spend'
                    when round(sum(i.INVOICE_AMOUNT))  is null then 'No spend'
                    else 'Wrong'
                    end as "Spend brackets last 12 months"
            ,case
                    when cont_all.CONTRACT_EFFECTIVE_DATE <='&end_date' then 'Contract'
                    when cont_all.CONTRACT_EFFECTIVE_DATE > '&end_date' then 'No Contract'
                    when cont_all.CONTRACT_EXPIRATION_DATE <= '&end_date' then 'No Contract'
                    when cont_all.CONTRACT_EXPIRATION_DATE > '&end_date' then 'Contract'
                    when cont_all.CONTRACT_EFFECTIVE_DATE is null then 'No Contract'
                    else 'Wrong'
                    end as "Contract Indicator"
--            ,case
--                    when
--                            round(sum(i.INVOICE_AMOUNT))  > 50000 and 
--                            (cont_all.CONTRACT_EFFECTIVE_DATE > '&end_date'
--                            or cont_all.CONTRACT_EXPIRATION_DATE <= '&end_date'
--                            or cont_all.CONTRACT_EFFECTIVE_DATE is null) 
--                            then 'Over 50k and no Contract'
--                    else 'Contract or No Contract with spend below 50k'
--                    end as "Contract Needed?"
            ,t.NAME "Terms"
            ,TO_CHAR(TO_DATE('&end_date', 'DD-MON-YYYY'), 'Mon YY') Month
            
from
            AP.AP_SUPPLIERS v
            join AP.AP_SUPPLIER_SITES_ALL s on v.VENDOR_ID=s.VENDOR_ID
            join apps.HR_ALL_ORGANIZATION_UNITS o on s.ORG_ID=o.ORGANIZATION_ID 
            left join AP.AP_INVOICES_ALL i on s.VENDOR_SITE_ID=i.VENDOR_SITE_ID
            left join AP_TERMS_TL t on s.TERMS_ID=t.TERM_ID
            left join APPS.OKC_REP_CONTRACT_PARTIES cont on v.VENDOR_ID=cont.PARTY_ID
            left join APPS.OKC_REP_CONTRACTS_ALL cont_all on cont.CONTRACT_ID=cont_all.CONTRACT_ID

where
            ((v.end_date_active is null or v.end_date_active > '&end_date') and (s.inactive_date is null or s.inactive_date > '&end_date'))
            and v.CREATION_DATE <= '&end_date'
            and s.ORG_ID in  ('324','288','325','285','301','296','302','294','327','305','304','298','303','297','299','300','295','309','307','316','312','314','317','313','318','315','320','319','322','323','326','321','308','310','311','306')
            and v.SEGMENT1 = 259456
            and i.CREATION_DATE between add_months('&end_date', -12) and '&end_date'
            and i.CANCELLED_DATE is null
                  
group by
            v.SEGMENT1
            ,v.VENDOR_NAME
            ,s.VENDOR_SITE_CODE
            ,case
                    when s.ORG_ID in ('285',    '296',  '294',  '327',  '304',  '297',  '295',  '312',  '313',  '315',  '319',  '322',  '321',  '306') then 'Fleet'
                    else 'Non-Fleet'
                    end
            ,case
                    when s.ORG_ID in ('288','325','285') then 'Belgium'
                    when s.ORG_ID in ('301','296','302','294') then 'Germany'
                    when s.ORG_ID in ('327','305','304') then 'Spain'
                    when s.ORG_ID in ('298','303','297','299','300','295') then 'France'
                    when s.ORG_ID in ('309','307') then 'Ireland'
                    when s.ORG_ID in ('316','312','314','317','313') then 'Italy'
                    when s.ORG_ID in ('318','315') then 'Luxembourg'
                    when s.ORG_ID in ('318','315') then 'Monaco'
                    when s.ORG_ID in ('322','323','324','326','321') then 'Netherlands'
                    when s.ORG_ID in ('308','310','311','306') then 'United Kingdom'
                    else 'Error'
                    end
            ,o.NAME
            ,case
                    when cont_all.CONTRACT_EFFECTIVE_DATE <='&end_date' then 'Contract'
                    when cont_all.CONTRACT_EFFECTIVE_DATE > '&end_date' then 'No Contract'
                    when cont_all.CONTRACT_EXPIRATION_DATE <= '&end_date' then 'No Contract'
                    when cont_all.CONTRACT_EXPIRATION_DATE > '&end_date' then 'Contract'
                    when cont_all.CONTRACT_EFFECTIVE_DATE is null then 'No Contract'
                    else 'Wrong'
                    end
--            ,case
--                    when
--                            (round(sum(i.INVOICE_AMOUNT))  > 50000
--                            and 
--                            (cont_all.CONTRACT_EFFECTIVE_DATE > '&end_date'
--                            or cont_all.CONTRACT_EXPIRATION_DATE <= '&end_date'
--                            or cont_all.CONTRACT_EFFECTIVE_DATE is null) )
--                            then 'Over 50k and no Contract'
--                    else 'Contract or No Contract with spend below 50k'
--                    end
            ,t.NAME
            ,TO_CHAR(TO_DATE('&end_date', 'DD-MON-YYYY'), 'Mon YY')

It's just a bit hard to tell, without seeing what you are grouping by. For example: if you don't include CONTRACT_EFFECTIVE_DATE in GROUP BY, why do you think your expression should be considered valid? Which of the (potentially different) effective dates, for different rows IN THE SAME GROUP, should be used for the comparison? — , Nov 12 '20 at 19:56
Agree with @mathguy; we need to see the GROUP BY and ORDER BY to better understand. If CONTRACT_EFFECTIVE_DATE and CONTRACT_EXPIRATION_DATE are not part of the GROUP BY, that will throw an error, but we're left to guess without more of the query. — CoffeeNeedCoffee, Nov 12 '20 at 20:02
@mathguy and CoffeeNeedCoffee, Thanks. I have included the full query in my question. — Adrian, Nov 12 '20 at 20:04
Unrelated, but `'No spend'` will never be reached, because it falls in the `<= 50000` bracket first (unless `null`) — HoneyBadger, Nov 12 '20 at 21:32

score 0 · Accepted Answer · answered Nov 12 '20 at 22:18

Here is your simplified example

create table  tab as
select 
1 SEGMENT1,
sysdate + rownum as CONTRACT_EFFECTIVE_DATE, rownum INVOICE_AMOUNT from dual 
connect by level <= 10;

select 
  SEGMENT1,
  case when sum(INVOICE_AMOUNT) > 50 and CONTRACT_EFFECTIVE_DATE > DATE'2020-01-01' then 'OK' else 'NOK' end as col1
from tab  
group by SEGMENT1;

This leads of course to an ORA-00979: not a GROUP BY expressionbecause you do not group by on CONTRACT_EFFECTIVE_DATE

But adding the CONTRACT_EFFECTIVE_DATE is most probably not what you want.

I guess you need to put the filter condition into the aggregate function, someting like this - so you aggregate INVOICE_AMOUNT only the if the condition is fullfiled and compares the result with the threshold

select 
  SEGMENT1,
  case when 
      sum(case when CONTRACT_EFFECTIVE_DATE > DATE'2020-01-01'then INVOICE_AMOUNT end) > 50  
  then 'OK' else 'NOK' end as col1
from tab  
group by SEGMENT1;

Thank you very much. Yes, you are right, I don't want to GROUP BY on CONTRACT_EFFECTIVE_DATE and your solution looks great! — Adrian, Nov 15 '20 at 18:38
@Adrian you are wolcome and of course you can additionaly upvote and/or acctept the answer. — Marmite Bomber, Nov 15 '20 at 22:49
Apologies. I am not very experienced with this forum. Upvoted and accepted. Thank you again! — Adrian, Nov 17 '20 at 09:26

Aggregate in case statement with "not a GROUP BY expression" error

1 Answers1