The schema of the table is: Emp (Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno)
Question:
Display the job, deptno and average salary of employees belonging to department 10 or 20 and their salary is more than 2000 and average salary is more than 2500.
My Query:
SELECT Job, Deptno, AVG(Sal) "AVGSALARY" FROM Emp WHERE Sal > 2000 AND Deptno IN(10, 20) GROUP BY Job HAVING AVG(Sal) > 2500;
Error: ORA-00979: not a GROUP BY expression
I cannot find what is wrong I am doing here that makes this error appear.
I am using the same column name in the GROUP BY
statement that I have mentioned in the SELECT
statement.
Also, the aggregate function I used in the HAVING
statement is also present in the SELECT
statement.
My question is ‘how can I remove this error from this statement?’. Any performance improvement query is also welcome.
Advertisement
Answer
You need to include all the non-aggregated columns in the GROUP BY statement. So include Deptno in the GROUP BY as well:
SELECT Job, Deptno, AVG(Sal) "AVGSALARY" FROM Emp WHERE Sal > 2000 AND Deptno IN(10, 20) GROUP BY Job, Deptno HAVING AVG(Sal) > 2500;