E.Saraf E.Saraf - 19 days ago 5
SQL Question

How to calculate percentage in column?

I've got a task to calculate smallest salary in the departments and how many percents is it from total salary.
Also i can use only one select statement and probably I need to use window functions.

This is how i started:

SELECT distinct department_id,
min(salary) over(partition by department_id) as min_sal
FROM employees;


But if I use RATIO_TO_REPORT function it show all table, not grouped by department_id.

Answer

Sounds like

select min(salary) as min_salary, round( min(salary)/sum(salary) * 100 , 1 ) as percent
from   employees
group by department_id
;

If you also need the employee's name who has the least salary in each department (assuming there's always only one), then add

    , min(empl_name) keep (dense_rank first order by salary) as min_sal_empl_name

to the SELECT statement. If there can be ties for the least salary, and you need all the employees, please say so - in that case you probably need analytic functions as you guessed. Something like:

select empl_name, salary, round(salary/tot_sal*100, 1) as percent
from   ( select empl_name, salary, 
                sum(salary) over (partition by department_id)            as tot_sal,
                rank() over (partition by department_id order by salary) as rn
         from   employees 
       )
where  rn = 1
;  

Added: OP indicated they need salary as percentage of total salary (across all departments) after all. This can be done by combining ratio_to_report with empty windowing condition (no "partition by" anything) with rank() partitioned by department to pick up least salary in each department.

The solution uses the EMP table in the SCOTT schema for testing, since the original post did not include sample data.

select deptno, empno, ename, sal, percent
from   (
         select empno, ename, sal, deptno,
                round(100 * ratio_to_report(sal) over (), 1)   as percent,
                rank() over (partition by deptno order by sal) as rn
         from   scott.emp
       )
where  rn = 1
;


DEPTNO  EMPNO  ENAME    SAL  PERCENT
------  -----  ------  ----  -------
10       7934  MILLER  1300      4.5
20       7369  SMITH    800      2.8
30       7900  JAMES    950      3.3
Comments