1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-00979: not a GROUP BY expression

Discussion in 'SQL PL/SQL' started by RIAZ, Apr 4, 2009.

  1. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    Please let me know what is the problem in following code,

    Code (Text):

    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
     
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Re: ORA-00979: not a GROUP BY expersion

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

    Try this
    Code (SQL):
    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
     
  3. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    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.
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    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.
     
  5. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    thank you.
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Another way of doing it without Group by but with Partition by :)
    This is Analytic function .

    Code (SQL):
     
    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>