1

I'm working on a homework problem where the unit deals with Subqueries. This is the only problem dealing with an aggregate function, and I don't know how to properly write the query. This is what my professor is asking-

"Create a query to display empno column, ename column, sal column, and the dept’s average sal data from emp table. Display all employees who have earned more than the same deptno’s average sal."

Here is what the correct output should look like from his example-

EMPN ENAME                  SAL   AVG(SAL)
---- --------------- ---------- ----------
7788 SCOTT                 3000  2916.66667
7839 KING                  5000  1566.66667
7698 BLAKE                 2850        2175
7782 CLARK                 2450  1566.66667
7566 JONES                 2975        2175
7902 FORD                  3000        2175

I'm so close to the correct answer, I think I need to use the 'deptno' somewhere in my code, but I don't know where/how. Here is my code-

SQL> SELECT empno, ename, sal, AVG(sal) AS "DEPT_AVG_SAL"
  2  FROM emp
  3  GROUP BY empno, ename, sal
  4  HAVING sal >
  5     (SELECT AVG(sal)
  6     FROM emp);

Here is the output I get. The 'sal' column is correct, just not the 'AVG(sal)'-

EMPN ENAME                  SAL DEPT_AVG_SAL
---- --------------- ---------- ------------
7788 SCOTT                 3000         3000
7839 KING                  5000         5000
7698 BLAKE                 2850         2850
7782 CLARK                 2450         2450
7566 JONES                 2975         2975
7902 FORD                  3000         3000

6 rows selected.

Thank you for anyone's help!

Improve this question

2 Answers 2

Reset to default
0

I would recommend putting the subquery in the FROM clause, because you need to refer to it twice.

The key idea is that you need something to get the average per department. Something like this:

SELECT e.empno, e.ename, e.sal, d.avg_sal
FROM emp e JOIN
     (SELECT e.deptid, AVG(sal) as avg_sal
      FROM emp e
      GROUP BY e.deptid
     ) d
     ON e.dept_id = d.dept_id
WHERE e.sal > d.avg_sal;

In particular, you do not need an outer GROUP BY, because you are not aggregating anything at the employee level.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

One minute and I will check.
It sure works. I really appreciate it. Looks very difficult. I don't understand it too much, though. There was no way for me to accomplish it by the way I wrote my code by using 'GROUP BY' and 'HAVING'?
0

You could approach this by computing a window average of salaries in the department in a subquery, and then filter in the outer query on employees whose salary is above the average. This avoids the use of a JOIN and of aggregation:

SELECT *
FROM (
    SELECT
        empno,
        ename,
        sal,
        AVG(sal) OVER(PARTITION BY deptno) dept_avg_sal
    FROM emp
) x
WHERE sal > dept_avg_sal
Improve this answer

5 Comments

Hi @GMB. Thanks for helping again. I'm positive your code works, but we never learned 'OVER' or the 'PARTITION BY' codes that you used.
@strudel: this is a window function (aka analytic function in Oracle terminology). What it does is perform an aggregate operation on a set of records, that is defined in the PARTITION clause, which is called a partition (and you dont need a GROUP BY clause). I would really recommend reading the above Oracle doc, analytics functions are very powerful and using them here seems to me like the most efficient way to adress your question.
Quick question- Your 2nd to last line, what does the 'x' mean?
It is just an alias for the derived table that is generated in the subquery. It is not mandatory in Oracle, but in other RDBMS it is, so it is a good practice to give an alias to every derived table.
Makes sense. I like your example too. I flipped through my book and "analytical functions" are introduced in a couple chapters from the one we're on.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.