Q:

Not a group by expression in my oracle SQL query

I have the following sql query that I m not able to process since this afternoon.
I have seen lot of threads about this issue but I m not getting it right, I believe that I m miss understanding this one topic that is shaming my day.
SELECT
    cor.c_order_id, cor.totallines,cor.documentno,cbp.name
FROM
    c_orderline   col
    LEFT JOIN c_order       cor ON cor.c_order_id = col.c_order_id
    LEFT join c_bpartner    cbp on cbp.c_bpartner_id = cor.c_bpartner_id
WHERE
    cor.issotrx = 'Y'
    and cor.docstatus not in ('DR','IP')
    AND cor.salesrep_id = 1037317
    and col.qtyordered <> 0
    and cor.dateordered between SYSDATE - 30  and SYSDATE + 30
    AND col.c_orderline_id NOT IN (
        SELECT
            cil.c_orderline_id
        FROM
            c_invoiceline cil
        WHERE
            cil.c_orderline_id IS NOT NULL
    )
    group by cor.c_order_id, cor.documentno
    order by cor.c_order_id, cor.documentno

What I m doing wrong ??

A:

A simplified example, based on Scott's EMP table.
This is what you did:
SQL> select deptno, job
  2  from emp
  3  group by deptno;
select deptno, job
               *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

As you can see, Oracle marked the culprit - the JOB column.
Therefore, if you want to use a GROUP BY clause (which is correct, but - you'd get the same result using DISTINCT; these two should not be used together. GROUP BY is usually used with aggregates such as min, max, avg and such), then put all columns into the GROUP BY:
SQL> select deptno, job
  2  from emp
  3  group by deptno, job;

    DEPTNO JOB
---------- ---------
        20 CLERK
        30 SALESMAN
        20 MANAGER
        30 CLERK
        10 PRESIDENT
        30 MANAGER
        10 CLERK
        10 MANAGER
        20 ANALYST

9 rows selected.

Or, as I said - using DISTINCT:
SQL> select distinct deptno, job
  2  from emp;

    DEPTNO JOB
---------- ---------
        20 CLERK
        30 SALESMAN
        20 MANAGER
        30 CLERK
        10 PRESIDENT
        30 MANAGER
        10 CLERK
        10 MANAGER
        20 ANALYST

9 rows selected.

SQL>

A:

group by is used for aggregate functions like sum(), max(), min() etc. What value are you calculating in groups?
remove the group by and your query looks good besides that

