Oracle strives for departmental employees ' wages as a percentage of total wages

--statistics on the sum of departments ' wages according to each department

Select DeptID, sum (SAL) payroll totals from the EMP group by DeptID;

--statistics on the sum of departments ' wages according to each department
Select DeptID, Payroll total, SUM (payroll total) over () as Total
From (select DeptID, sum (SAL) payroll total from EMP Group by DeptID) x;



Select
DeptID Department,
Total wage,
Total Sum,
Round (Total payroll/total) * 100, 2) | | '% ' as wage ratio
From (select DeptID,
Total wage,
SUM (payroll total) over () as total from (select DeptID,
SUM (SAL) Payroll total
From EMP
Group by DeptID) x) y
Order by 1;


--round (number,2) Keep the following two decimal places rounded by a decimal number after interception
Select Round (2342.54665,1) from dual;


--Use a dedicated scale function
Select DeptID,
Total wage,
SUM (total wage) over () as Total,
Round (Ratio_to_report (total wage) over () * 100, 2) | | '% ' as wage ratio
From (select DeptID, sum (SAL) payroll total from EMP Group by DeptID)
ORDER BY 1 Desc;


--use analytic functions to query the salary ratio of each employee in the corresponding department
Select DeptID,
ENAME,
Sal
Round (Ratio_to_report (SAL) over (partition by DeptID) * 100, 2) | | '% ' wage ratio
From EMP
Order by 1, 2;

Oracle strives for departmental employees ' wages as a percentage of total wages