select 
    case 
        when EMP.JOB in ('CLERK','SALESMAN') 
            then listagg (DISTINCT EMP.SAL, ';') within group (order by EMP.EMPNO)
    end as sal
from 
    EMP
where 
    EMP.COMM is not null
group by 
    EMP.EMPNO

I am getting this error

ORA-00979: not a GROUP BY expression' when running this query.

This is the database to run this query:

CREATE TABLE EMP
(
    EMPNO NUMBER(4) NOT NULL,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7, 2),
    COMM NUMBER(7, 2),
    DEPTNO NUMBER(2)
);

INSERT INTO EMP 
VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP 
VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP 
VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP 
VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP 
VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP 
VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP 
VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP 
VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP 
VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP 
VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);
INSERT INTO EMP 
VALUES (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP 
VALUES (7900, 'JAMES', 'CLERK', 7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP 
VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP 
VALUES (7934, 'MILLER', 'CLERK', 7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CodePudding user response：

The way you've written the code, you would need to group by EMP.JOB - that not being included is what the error is referring to:

select
  case
    when EMP.JOB in ('CLERK','SALESMAN')
    then LISTAGG (DISTINCT EMP.SAL, ';') WITHIN GROUP ( ORDER BY EMP.EMPNO)
  end as sal
from EMP
where EMP.COMM is not null
group by EMP.EMPNO, EMP.JOB

That doesn't seem to be what you want, though, since it doesn't actually aggregate - well, it does, but as each employee has a single salary and job, you get one row for each employee, with its single salary aggregated, which is the same as not aggregating.

You could put the case expression inside the listagg() instead:

select
  LISTAGG (
    DISTINCT case
      when EMP.JOB in ('CLERK','SALESMAN')
      then EMP.SAL
      end, 
    ';')
    WITHIN GROUP (ORDER BY EMP.EMPNO) as sal
from EMP
where EMP.COMM is not null;

which works but the order looks strange - it doesn't really make sense to order by the employee ID, particularly when you're eliminating duplicates. (I'm slightly surprised it doesn't complain - as you have a distinct salary, if that applies to more than one EMPNO, which one does it use in the ordering?)

Something like this is simpler, and I've switched the order to be in SAL order, which looks a bit more useful perhaps:

select
  LISTAGG (DISTINCT EMP.SAL, ';') WITHIN GROUP (ORDER BY EMP.SAL) as sal
from EMP
where EMP.JOB in ('CLERK','SALESMAN')
and EMP.COMM is not null;

fiddle

(Maybe still slightly surprising that it doesn't mind the DISTINCT and ORDER BY referring to the same column, but at least it's less ambiguous now...)