How to remove "not a GROUP BY expression" error from the statement?

0
votes

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.

1
sqloraclegroup-by

1 Answers

0
votes

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;