Encountered Error: ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression"

Question

I want to join 3 table together and display the output as below.But i encountered the error when i tried to use group by and order by together. Is there any solution can solve this error? Below is my Oracle query. Appreciate if someone could help on this error. Thank you.

1. The database data is like below: This is my 1st table (RMSTMP_PNG.ota_activity_lotinfo):

       ID                           KEY               VALUE
AD9FBCCC49F06446E050280A2         Quantity            100
AD9FBCCC49F06446E050280A2        PackageLeads         BN1140
AD9FBCCC49F06446E050280A2        TestProgram          DL_FP5_SLT
AD9FBCCC49F06446E050280A2          TestRev            3.0.0
AD9FBCCC49F06446E050280A2           IDV               9651AA43

Second Table as below (RMSTMP_PNG.ota_activity):

EQP_ID  LOT_ID   ACTIVITY  MODIFIED BY  MODIFIED DATE              ID
LAS43   9KA2909  LOAD_LOT  jratnasa     18-AUG-20       AD9FBCCC49F06446E050280A2

Third Table (RMSTMP_PNG.ota_activity_rescreen)

       ID                        RESCREEN TYPE
AD9FBCCC49F06446E050280A2           FRESH

2. The output i want is group all same ID as below and display in gridview but i am not sure is it possible to do that?

The final output should be like this:

EQP_ID   MODIFIED DATE         ID                            name
LAS43    18-AUG-20       AD9FBCCC49F06446E050280A2        Quantity:100,
                                                          PackageLeads:BN1140,
                                                          TestProgram:DL_FP5_SLT,  
                                                          TestRev:3.0.0,                                
                                                          IDV:9651AA43

3. i come up with this query, but seem like it does not working.

SELECT RMSTMP_PNG.ota_activity.EQP_ID,
       RMSTMP_PNG.ota_activity.MODIFIED_DATE,
       RMSTMP_PNG.ota_activity_lotinfo.ID,
       LISTAGG(RMSTMP_PNG.ota_activity_lotinfo.KEY) 
              WITHIN GROUP (ORDER BY RMSTMP_PNG.ota_activity_lotinfo.KEY) as "names"
from RMSTMP_PNG.ota_activity 
INNER JOIN RMSTMP_PNG.ota_activity_rescreen 
    ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_rescreen.ID 
INNER JOIN RMSTMP_PNG.ota_activity_lotinfo 
    ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_lotinfo.ID 
WHERE RMSTMP_PNG.ota_activity.MODIFIED_DATE BETWEEN'01-Jan-2020'AND '30-Sep-2020'
GROUP BY RMSTMP_PNG.ota_activity_lotinfo.ID,
         RMSTMP_PNG.ota_activity_lotinfo.KEY,
         RMSTMP_PNG.ota_activity.EQP_ID
order by RMSTMP_PNG.ota_activity.MODIFIED_DATE ASC

Answer 1

Try below. Check your SELECT and GROUP BY statements always with the ORA-00979: not a GROUP BY

Here some other usages of LISTAGG in case you need them.

  SELECT RMSTMP_PNG.ota_activity.EQP_ID,
         RMSTMP_PNG.ota_activity.MODIFIED_DATE,
         RMSTMP_PNG.ota_activity_lotinfo.ID,
         LISTAGG (RMSTMP_PNG.ota_activity_lotinfo.KEY)
            WITHIN GROUP (ORDER BY RMSTMP_PNG.ota_activity_lotinfo.KEY)
            AS "names"
    FROM RMSTMP_PNG.ota_activity
         INNER JOIN RMSTMP_PNG.ota_activity_rescreen
            ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_rescreen.ID
         INNER JOIN RMSTMP_PNG.ota_activity_lotinfo
            ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_lotinfo.ID
   WHERE RMSTMP_PNG.ota_activity.MODIFIED_DATE BETWEEN '01-Jan-2020'
                                                   AND '30-Sep-2020'
GROUP BY RMSTMP_PNG.ota_activity_lotinfo.ID,
         RMSTMP_PNG.ota_activity.MODIFIED_DATE,
         RMSTMP_PNG.ota_activity.EQP_ID
ORDER BY RMSTMP_PNG.ota_activity.MODIFIED_DATE ASC

As your intended output try below. I did not know what which id you need and where is the VALUE column comes from. I know you can manage though.

 SELECT RMSTMP_PNG.OTA_ACTIVITY.EQP_ID AS ID,
         LISTAGG (RMSTMP_PNG.OTA_ACTIVITY_LOTINFO.KEY || ':' || VALUE, ',')
            WITHIN GROUP (ORDER BY RMSTMP_PNG.OTA_ACTIVITY.MODIFIED_DATE)
            AS "names"
    FROM RMSTMP_PNG.OTA_ACTIVITY
         INNER JOIN RMSTMP_PNG.OTA_ACTIVITY_RESCREEN
            ON RMSTMP_PNG.OTA_ACTIVITY.ID = RMSTMP_PNG.OTA_ACTIVITY_RESCREEN.ID
         INNER JOIN RMSTMP_PNG.OTA_ACTIVITY_LOTINFO
            ON RMSTMP_PNG.OTA_ACTIVITY.ID = RMSTMP_PNG.OTA_ACTIVITY_LOTINFO.ID
   WHERE RMSTMP_PNG.OTA_ACTIVITY.MODIFIED_DATE BETWEEN '01-Jan-2020'
                                                   AND '30-Sep-2020'
GROUP BY RMSTMP_PNG.OTA_ACTIVITY_LOTINFO.ID
ORDER BY RMSTMP_PNG.OTA_ACTIVITY.MODIFIED_DATE