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
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
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