ORA-00979: not a GROUP BY expression

[RIAZ]

Please let me know what is the problem in following code,

SELECT rc.CUSTOMER_ID, rc.CUSTOMER_NAME, rc.STATUS,
rcta.TRX_DATE, rcta.TRX_NUMBER,
rctta.NAME,
sum(rctla.EXTENDED_AMOUNT) DR
FROM RA_CUSTOMERS rc,
RA_CUSTOMER_TRX_ALL rcta,
RA_CUST_TRX_TYPES_ALL rctta,
RA_CUSTOMER_TRX_LINES_ALL rctla
WHERE rc.CUSTOMER_NAME = 'ABC'
AND rc.CUSTOMER_ID = rcta.BILL_TO_CUSTOMER_ID
AND rcta.COMPLETE_FLAG = 'Y'
AND rctta.CUST_TRX_TYPE_ID = rcta.CUST_TRX_TYPE_ID
AND rctla.CUSTOMER_TRX_ID = rcta.CUSTOMER_TRX_ID
group by rcta.TRX_NUMBER

[tyro]

hi Riaz, you have to use all the columns you are using in your select statement within your group by clause

Try this

SELECT   rc.customer_id, rc.customer_name, rc.STATUS, rcta.trx_date,
         rcta.trx_number, rctta.NAME, SUM (rctla.extended_amount) dr
    FROM ra_customers rc,
         ra_customer_trx_all rcta,
         ra_cust_trx_types_all rctta,
         ra_customer_trx_lines_all rctla
   WHERE rc.customer_name = 'ABC'
     AND rc.customer_id = rcta.bill_to_customer_id
     AND rcta.complete_flag = 'Y'
     AND rctta.cust_trx_type_id = rcta.cust_trx_type_id
     AND rctla.customer_trx_id = rcta.customer_trx_id
 GROUP BY rc.customer_id,
         rc.customer_name,
         rc.STATUS,
         rcta.trx_date,
         rcta.trx_number,
         rctta.NAME

[RIAZ]

It works, thanks. Well i could not understand the logic behind this, would you please explain why we need to add all the select cols into group by clause?

In my understanding we should only write the col in group by clause that is required for grouping. Is not it ? This logic works well with a single table.

[tyro]

you are using the aggregate function SUM. When you use a group by clause it divides your resultant rows into sets and then the aggregate function is used on all the sets. So when you selecting multiple columns in your select statement, the grouping also has to be based on all those columns.

[RIAZ]

thank you.

[rajavu]

Another way of doing it without Group by but with Partition by
This is Analytic function .

SQL> SELECT DEPTNO,JOB, SUM(SAL)
  2  FROM EMP
  3  GROUP BY DEPTNO,JOB;
 
    DEPTNO JOB         SUM(SAL)
 ---------- --------- ----------
        20 CLERK           1900
        30 SALESMAN        5600
        20 MANAGER         2975
        30 CLERK            950
        10 PRESIDENT       5000
        30 MANAGER         2850
        10 CLERK           1300
        10 MANAGER         2450
        20 ANALYST         6000
 
 9 rows selected.
 
 SQL> SELECT DISTINCT  DEPTNO,JOB ,
  2         SUM(SAL)  OVER  (PARTITION BY DEPTNO,JOB )
  3  FROM EMP   ;
 
    DEPTNO JOB       SUM(SAL)OVER(PARTITIONBYDEPTNO,JOB)
 ---------- --------- -----------------------------------
        10 CLERK                                    1300
        30 SALESMAN                                 5600
        20 MANAGER                                  2975
        30 MANAGER                                  2850
        10 PRESIDENT                                5000
        20 CLERK                                    1900
        20 ANALYST                                  6000
        30 CLERK                                     950
        10 MANAGER                                  2450
 
 9 rows selected.
 
 SQL>