how to solve " ORA-00979: not a GROUP BY expression " Error

Question

I'm trying to run this query and I get "not a group by expression"

SELECT   
   PS.SEGMENT1,  
   PSSI.VENDOR_NAME,
   PSSI.VENDOR_SITE_CODE, 
   AIPA.PAYMENT_NUM,
   AIPA.AMOUNT,
   AIPA.PAYMENT_CURRENCY_CODE,
   AIA.INVOICE_AMOUNT,
   AIA.INVOICE_NUM,  
   AIA.DESCRIPTION,
       AIA.INVOICE_DATE  
FROM 
     POZ_SUPPLIERS PS,  
     POZ_SUPPLIER_SITES_INT PSSI,
     AP_INVOICE_PAYMENTS_ALL AIPA,
     AP_INVOICES_ALL AIA 


  WHERE PSSI.VENDOR_ID=PS.VENDOR_ID
  AND AIA.VENDOR_ID=PS.VENDOR_ID
  AND AIPA.INVOICE_ID=AIA.INVOICE_ID

group by AIPA.AMOUNT

can anyone point me in the right direction ?

First, first your `join` syntax. Then add all the other columns to the `group by`. — Gordon Linoff, Oct 18 '17 at 18:45
First of all, use proper `JOIN`. Secondly, you don't have aggregation, why use `GROUP BY`? Third, all non-aggregated columns (which is all columns in this case), must be in the `GROUP BY` — Eric, Oct 18 '17 at 18:49
The general GROUP BY rule says: "If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function." — jarlh, Oct 18 '17 at 18:52
I'm guessing he is trying to sum the amount and is using the syntax really wrong. Answer up if my guess is right. @eric - He is using old syntax, just as valid and produces same run plans...no need to go at him for that. — Twelfth, Oct 18 '17 at 18:57
Group by w/o an aggregate doesn't make much sense. you need a [max, sum, min, avg, etc](https://docs.oracle.com/database/121/SQLRF/functions003.htm#SQLRF20035). some sort of aggregation and then a group by makes sense. and you group by all the fields which are NOT being aggregated and in your select. — xQbert, Oct 18 '17 at 19:03
@Soha Mahmoud - I made a mistake in my answer...please review my edit as the mistake will give you odd results. — Twelfth, Oct 18 '17 at 19:19

Twelfth · Accepted Answer · 2017-10-18T19:15:35.840

Group by will not auto sum for you...I'm kinda guessing this is what you want

SELECT   
PS.SEGMENT1,  
PSSI.VENDOR_NAME,
PSSI.VENDOR_SITE_CODE, 
AIPA.PAYMENT_NUM,
sum(AIPA.AMOUNT) as totalamount,
AIPA.PAYMENT_CURRENCY_CODE,
AIA.INVOICE_AMOUNT,
AIA.INVOICE_NUM,  
AIA.DESCRIPTION,
   AIA.INVOICE_DATE  
FROM 
 POZ_SUPPLIERS PS,  
 POZ_SUPPLIER_SITES_INT PSSI,
 AP_INVOICE_PAYMENTS_ALL AIPA,
 AP_INVOICES_ALL AIA 


WHERE PSSI.VENDOR_ID=PS.VENDOR_ID
AND AIA.VENDOR_ID=PS.VENDOR_ID
AND AIPA.INVOICE_ID=AIA.INVOICE_ID

group by     PS.SEGMENT1,  
PSSI.VENDOR_NAME,
PSSI.VENDOR_SITE_CODE, 
AIPA.PAYMENT_NUM,
AIPA.PAYMENT_CURRENCY_CODE,
AIA.INVOICE_AMOUNT,
AIA.INVOICE_NUM,  
AIA.DESCRIPTION,
   AIA.INVOICE_DATE

The sum(amount) in the select statement will do the sum for you. You then need to group by the rest of the static columns

Edit:

I accidentally left AIPA.AMOUNT in the group by. I editted my answer, make sure AIPA.AMOUNT is not in your group by clause

how to solve " ORA-00979: not a GROUP BY expression " Error

1 Answers1