CODEWITHSUNDEEP
sqloracle-databaseoracle10g
dcray89
dcray89

Reputation: 13

Combining columns

I am trying to do the following: I have a table with ename, job, deptno, and sal. I am trying to initiate a query that returns the top earners of each department. I have done this with grouping and a subquery. However, I also want to display the average sal by deptno. So the following would be the result:

"ename"  "dept"  "sal"  "average of dept"
sal      20      1000   500   
kelly    30      2000   800 
mika     40      3000   400

this might be impossible since the average does not associate with the other rows.

any suggestion would be appreciated. Thanks. I am using Oracle 10g to run my queries.

Upvotes: 1

Views: 141

Answers (3)

Andriy M
Andriy M

Reputation: 77687

You could use analytic functions:

WITH RankedAndAveraged AS (
  SELECT
    ename,
    dept,
    sal,
    RANK() OVER (PARTITION BY dept ORDER BY sal DESC) AS rnk,
    AVG(sal) OVER (PARTITION BY dept) AS "average of dept"
  FROM atable
)
SELECT
  ename,
  dept,
  sal,
  "average of dept"
FROM RankedAndAveraged
WHERE rnk = 1

This may return more than one employee per department if all of them have the same maximum value of sal. You can replace RANK() with ROW_NUMBER() if you only want one person per department (in which case you could also further extend ORDER BY by specifying additional sorting criteria to pick the top item, otherwise it will be picked randomly from among those with the maximum salary).

Upvotes: 2

pomel
pomel

Reputation: 410

This should work. The only trick is that if you have several employees with the maximum salary in a department, it will show all of them.

SELECT t.ename, t.deptno, mx.sal as sal, mx.avg_sal as avg_sal
FROM tbl t, 
  (SELECT MAX(sal) AS sal, AVG(sal) AS avg_sal, deptno
   FROM tbl
   GROUP BY deptno) mx
WHERE t.deptno = mx.deptno AND t.sal = mx.sal

Upvotes: 1

Amadan
Amadan

Reputation: 198334

Not sure about Oracle, haven't used it in about 10 years, but something like this should be possible:

SELECT
  ename, deptno, sal,
  (SELECT AVG(T2.sal)
    FROM tbl T2
    WHERE T2.deptno = T.deptno
  ) AS average_of_dept
FROM tbl T
GROUP BY deptno
HAVING sal = MAX(sal)

Upvotes: 0

Related Questions