Getting percent of employee's salary within department-CodePudding

I need to create a report that displays information about what part of the salary in percentage terms each employee receives within the department in which they work, using analytical functions.

SELECT EMPLOYEES.FIRST_NAME, EMPLOYEES.LAST_NAME, EMPLOYEES.DEPARTMENT_ID, 
DEPARTMENTS.DEPARTMENT_NAME, EMPLOYEES.SALARY,
(SALARY/SUM(SALARY)) * 100  over (partition by DEPARTMENT_ID) AS "PercentWithinDepartment"
FROM HR.EMPLOYEES
FULL JOIN HR.DEPARTMENTS ON EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID

I get an "ORA-00923 FROM keyword not found where expected" error but I think it's not my only mistake within this task.

I cannot provide a code snippet of database but this can be run against the HR sample schema.

enter image description here

My request is to help me figure out mistake to complete this task properly.

CodePudding user response：

The immediate issue is that your over clause is in the wrong place:

(SALARY/SUM(SALARY)) * 100  over (partition by DEPARTMENT_ID)

should be

(SALARY/SUM(SALARY) over (partition by DEPARTMENT_ID)) * 100

But the reference to DEPARTMENT_ID in there is ambiguous as that column is in both tables, so it should be:

(SALARY/SUM(EMPLOYEES.SALARY) over (partition by DEPARTMENTS.DEPARTMENT_ID)) * 100

I'm not sure you really want a full outer join though.

CodePudding user response：

I don't have HR schema (but I have Scott) so - here's how. I'm using SQL*Plus' formatting capabilities to make it look prettier.

SQL> set numformat 999g990d00
SQL> break on deptno on dname
SQL> compute sum of pct_sal on deptno
SQL>
SQL> select e.deptno, d.dname, e.ename, e.sal,
  2    sum(e.sal) over (partition by e.deptno) dept_sal,
  3    --
  4    round((e.sal / sum(e.sal) over (partition by e.deptno)) * 100, 2) pct_sal
  5  from emp e join dept d on d.deptno = e.deptno
  6  order by e.deptno, e.ename;

Result:

     DEPTNO DNAME          ENAME              SAL    DEPT_SAL     PCT_SAL
----------- -------------- ---------- ----------- ----------- -----------
      10,00 ACCOUNTING     CLARK         2.450,00    8.750,00       28,00
                           KING          5.000,00    8.750,00       57,14
                           MILLER        1.300,00    8.750,00       14,86
*********** **************                                    -----------
sum                                                                100,00
      20,00 RESEARCH       ADAMS         1.100,00   10.915,00       10,08
                           FORD          3.000,00   10.915,00       27,49
                           JONES         2.975,00   10.915,00       27,26
                           SCOTT         3.000,00   10.915,00       27,49
                           SMITH           840,00   10.915,00        7,70
*********** **************                                    -----------
sum                                                                100,02
      30,00 SALES          ALLEN         1.600,00    9.400,00       17,02
                           BLAKE         2.850,00    9.400,00       30,32
                           JAMES           950,00    9.400,00       10,11
                           MARTIN        1.250,00    9.400,00       13,30
                           TURNER        1.500,00    9.400,00       15,96
                           WARD          1.250,00    9.400,00       13,30
*********** **************                                    -----------
sum                                                                100,01

14 rows selected.

SQL>

(Sum isn't exactly 100% because of rounding.)