Group By in Oracle is not working

Question

I am trying to use group by in with all fields in select too.

My query:

SELECT count(*) as totalRow,
       trim(A.P_CODE) P_CODE,
       INITCAP(trim(A.ALTERNATE_TEXT)) ALTERNATE_TEXT,
       trim(A.LINKED_SKU_CODE) as LINKED_SKU_CODE,
       UCWORD(B.BRAND_NAME) BRAND_NAME,
       LOWER(TRIM(A.UNIT)) UNIT,
       INITCAP(TRIM(A.PK_SIZE)) PK_SIZE,
       trim(A.DF_SALE_RATE) DF_SALE_RATE,
       trim(A.MRP) MRP,
       INITCAP(trim(A.CAT_TYPE)) CAT_TYPE,
       NVL(MAX_QTY,25) MAX_QTY,
       A.BAL_QTY 
FROM GET_PRODUCT A,
     WEB_BRANDS B 
WHERE A.P_CODE in (".$p_codes.") 
  AND A.BR_CODE = '".BR_CODE."' 
  AND A.BRAND_CODE = B.BRAND_CODE 
GROUP BY A.P_CODE,
         A.ALTERNATE_TEXT,
         A.LINKED_SKU_CODE,
         B.BRAND_NAME,
         A.UNIT,
         A.PK_SIZE,
         A.DF_SALE_RATE,
         A.MRP,
         A.CAT_TYPE,
         MAX_QTY,
         A.BAL_QTY  
ORDER BY CAT_TYPE,
         P_NAME

Error:

ORA-00979: not a GROUP BY expression in C:

Please point me in right direction or could point me where I have done mistakes.

http://stackoverflow.com/search?q=[oracle]+ORA-00979%3A+not+a+GROUP+BY+expression — , Sep 09 '15 at 07:20
possible duplicate of [SQL not a GROUP BY expression with OracleSQL and InnerQuery Error](http://stackoverflow.com/questions/30700316/sql-not-a-group-by-expression-with-oraclesql-and-innerquery-error) — Praveen, Sep 09 '15 at 07:22
The first error I see is that you have `trim(A.P_CODE)` in your select and not in your `GROUP BY` so that isn't supposed to work — Wolph, Sep 09 '15 at 07:41
@EatÅPeach: indeed there is, but no `trim(A.P_CODE)`. If he would be selecting `A.P_CODE` that would have helped — Wolph, Sep 09 '15 at 09:56
But, that shouldn't give error. http://sqlfiddle.com/#!4/38496/2 — Noel, Sep 09 '15 at 11:13

score -1 · Answer 1 · edited Sep 09 '15 at 09:46

You should write in GROUP BY section at least all columns from SELECT section ( without aggregatable columns). I think you query should look like

SELECT count(*) as totalRow,
       trim(A.P_CODE) P_CODE,
       INITCAP(trim(A.ALTERNATE_TEXT)) ALTERNATE_TEXT,
       trim(A.LINKED_SKU_CODE) as LINKED_SKU_CODE,
       UCWORD(B.BRAND_NAME) BRAND_NAME,
       LOWER(TRIM(A.UNIT)) UNIT,
       INITCAP(TRIM(A.PK_SIZE)) PK_SIZE,
       trim(A.DF_SALE_RATE) DF_SALE_RATE,
       trim(A.MRP) MRP,
       INITCAP(trim(A.CAT_TYPE)) CAT_TYPE,
       NVL(MAX_QTY,25) MAX_QTY,
       A.BAL_QTY 
FROM GET_PRODUCT A,
     WEB_BRANDS B 
WHERE A.P_CODE in (".$p_codes.") 
  AND A.BR_CODE = '".BR_CODE."' 
  AND A.BRAND_CODE = B.BRAND_CODE 
  GROUP BY 
  trim(A.P_CODE) ,
        INITCAP(trim(A.ALTERNATE_TEXT)),
        trim(A.LINKED_SKU_CODE) ,
        UCWORD(B.BRAND_NAME) ,
        LOWER(TRIM(A.UNIT)),
        INITCAP(TRIM(A.PK_SIZE)),
        trim(A.DF_SALE_RATE) ,
        trim(A.MRP) MRP,
        INITCAP(trim(A.CAT_TYPE)) ,
        NVL(MAX_QTY,25),
        A.BAL_QTY

i have already added all the fields in group by in my query i got solution the problem is the fields which are used in ORDER BY statement CAT_TYPE and P_NAME from which i have not included P_NAME once i included this in group by query gives results with no error. — Pravin Vanmore, Sep 10 '15 at 08:27

Group By in Oracle is not working

1 Answers1