About “group function is not allowed here”

I have a table with job, salary and date columns. I am writing the following query in PL/SQL, but I am getting an error

group function is not allowed here

delete employees where date = '06-05-2020 'and avg (salary)> 5500;

How can I solve this problem?

Answer

Your query makes no sense (to me, at least). What does that average salary represent? Whose average salary?

Here’s an example based on Scott’s EMP table; I’m going to delete employees who were hired on 3th of December 1981 and work in department whose employees’ average salary is higher than 2000.

Sample data:

SQL> select deptno, ename, sal, hiredate from emp order by deptno, ename;

    DEPTNO ENAME             SAL HIREDATE
---------- ---------- ---------- ----------
        20 ADAMS            1100 12.01.1983
        20 FORD             3000 03.12.1981   --> this
        20 JONES            2975 02.04.1981
        20 SCOTT            3000 09.12.1982
        20 SMITH             800 17.12.1980
        30 ALLEN            1600 20.02.1981
        30 BLAKE            2850 01.05.1981
        30 JAMES             950 03.12.1981   --> this
        30 MARTIN           1250 28.09.1981
        30 TURNER           1500 08.09.1981
        30 WARD             1250 22.02.1981

11 rows selected.

Averege salaries per department:

SQL> select deptno, avg(sal) avg_salary
  2  from emp
  3  group by deptno
  4  order by avg_salary desc;

    DEPTNO AVG_SALARY
---------- ----------
        20       2175         --> higher than 2000
        30 1566,66667

So: I’m looking for employees who work in department 20 (as only that department has average salaries higher than 2000) and who were hired on 03.12.1981 (James and Ford, but only Ford works in department 20):

SQL> delete from emp
  2  where hiredate = date '1981-12-03'
  3    and deptno in (select deptno
  4                   from emp
  5                   group by deptno
  6                   having avg(sal) > 2000
  7                  );

1 row deleted.

Is Ford still in there?

SQL> select * From emp where ename = 'FORD';

no rows selected

SQL>

Nope, deleted.

---

Now, your turn.