Employees with the lower salary than the average in their dept-CodePudding

I got two challenges on my college exam to do. The first one is to Show data for employees (name, salary, and department) who are paid less than the average salary for their department. I tried this:

SELECT ename, sal, deptno
FROM Emp
WHERE sal < (SELECT AVG(sal) FROM EMP)
GROUP BY deptno;

But i get the ORA-00979: not a GROUP BY expression error.

The other one i didn't started but is quite the same, Show the data of employees (name, commission and department) who receive commission greater than the average commission in their department.

ps: I'm starting with SQL so the topic isn't very deep yet.

CodePudding user response：

You can use a window function for this

SELECT ename, sal, deptno
FROM (
    SELECT *,
      AVG(sal) OVER (PARTITION BY deptno) AS AvgPerDept
    FROM Emp
) AS Emp
WHERE sal < AvgPerDept

CodePudding user response：

Your attempt to the first one is not correct

The expression SELECT AVG(sal) FROM EMP will give the average salary of everyone, regardless which department they're in.

To get the average for each deparment, you will need to GROUP BY dept_no

SELECT Emp.* , dept_salary.average_sal
FROM Emp
INNER JOIN
-- join with an average salary for each department
(
    SELECT deptno, AVG(sal) as average_sal
    FROM Emp
    GROUP BY deptno
) dept_salary
ON Emp.deptno = dept_salary.deptno
WHERE Emp.dept_no = dept_salary.deptno AND Emp.sal < dept_salary.average_sal;