I have two tables emp and dept I need to find the names of departments where employees earn the most and the least on average and display the result in one line with the difference.
here is my solution
select 'most earned ' || a.dname|| ' least earned ' ||b.dname|| ' difference ' || (a.salary - b.salary)
from (select d.dname, avg(sal) as salary
from dept d join emp e on d.deptno = e.deptno
group by d.dname) a,
(select d.dname, avg(sal) as salary
from dept d join emp e on d.deptno = e.deptno
group by d.dname) b
where a.salary = (select max(salary)
from (select d.dname, avg(sal) as salary
from dept d join emp e on d.deptno = e.deptno
group by d.dname))
AND b.salary = (select min(salary)
from (select d.dname, avg(sal) as salary
from dept d join emp e on d.deptno = e.deptno
group by d.dname))
this works but I was wondering if there was a cleaner way of writing this query ? any help is appreciated
Here are the tables:
CREATE TABLE DEPT
(DEPTNO NUMBER(2) PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE EMP
(EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4) REFERENCES EMP,
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2) REFERENCES DEPT);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('17-MAR-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2-MAR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839,
TO_DATE('1-MAR-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839,
TO_DATE('9-MAR-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('20-MAR-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('22-MAR-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
TO_DATE('28-MAR-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('09-MAR-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-MAR-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('12-MAR-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698,
TO_DATE('3-MAR-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566,
TO_DATE('3-MAR-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-MAR-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-MAR-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
CodePudding user response：
Here's one option, which uses
rankanalytic function (to "sort" average salaries in ascending (least) or descending (most) order),caseexpression (to find departments that earns the most or the least) and- some joins
SQL> WITH
2 salaries
3 AS
4 ( SELECT e.deptno,
5 ROUND (AVG (e.sal)) avg_sal,
6 --
7 CASE
8 WHEN RANK () OVER (ORDER BY AVG (e.sal) DESC) = 1
9 THEN
10 e.deptno
11 END max_deptno,
12 CASE
13 WHEN RANK () OVER (ORDER BY AVG (e.sal) ASC) = 1
14 THEN
15 e.deptno
16 END min_deptno
17 FROM emp e
18 GROUP BY e.deptno)
19 SELECT 'most earned '
20 || a.dname
21 || ', least earned '
22 || b.dname
23 || '; difference = '
24 || TO_CHAR (sa.avg_sal - sb.avg_sal) AS result
25 FROM salaries sa
26 JOIN dept a ON a.deptno = sa.max_deptno
27 CROSS JOIN salaries sb
28 JOIN dept b ON b.deptno = sb.min_deptno;
RESULT
--------------------------------------------------------------------------------
most earned ACCOUNTING, least earned SALES; difference = 1350
SQL>
CodePudding user response：
You can do it only selecting from each table once and without using self-joins:
SELECT 'Most earned: '||TO_CHAR(MAX(avg_sal), 'fm99999990.00')
||' in '||LISTAGG(CASE avg_sal_rank_desc WHEN 1 THEN dname END, ',')
WITHIN GROUP (ORDER BY dname)
||', Least earned: '||TO_CHAR(MIN(avg_sal), 'fm99999990.00')
||' in '||LISTAGG(CASE avg_sal_rank_asc WHEN 1 THEN dname END, ',')
WITHIN GROUP (ORDER BY dname)
||', Difference: '||TO_CHAR(MAX(avg_sal)-MIN(avg_sal), 'fm99999990.00')
AS average_salary_details
FROM (
SELECT d.deptno,
MAX(d.dname) AS dname,
COALESCE(AVG(e.sal), 0) AS avg_sal,
RANK() OVER (ORDER BY COALESCE(AVG(e.sal), 0) ASC) AS avg_sal_rank_asc,
RANK() OVER (ORDER BY COALESCE(AVG(e.sal), 0) DESC) AS avg_sal_rank_desc
FROM dept d
LEFT OUTER JOIN emp e
ON (d.deptno = e.deptno)
GROUP BY d.deptno
)
WHERE avg_sal_rank_asc = 1
OR avg_sal_rank_desc = 1
Note: If there are multiple departments tied for highest or lowest average salary then this will output them all in a comma-delimited list.
Which, for the sample data, outputs:
AVERAGE_SALARY_DETAILS Most earned: 2916.67 in ACCOUNTING, Least earned: 0.00 in OPERATIONS, Difference: 2916.67
If you do not want to include departments with zero employees then change the LEFT OUTER JOIN to an INNER JOIN and then the output is:
AVERAGE_SALARY_DETAILS Most earned: 2916.67 in ACCOUNTING, Least earned: 1566.67 in SALES, Difference: 1350.00
db<>fiddle here