Department-wise Salary Percentages in Oracle SQL

Topic Introduction: In this tutorial, we will show SQL for calculating salary contributions by each department. Have various ways to find out Department-wise Salary Percentages in Oracle SQL. Follow the below SQL code. 

Method #1

  SELECT d.department_name,

         SUM (e.salary) dept_salary,

         ROUND (ratio_to_report (SUM (e.salary)) OVER () * 100, 2) salary_pct

    FROM employees e, departments d

   WHERE e.department_id = d.department_id

GROUP BY d.department_name;

Method #2

SELECT DISTINCT

       d.department_name,

         (SUM (e.salary) OVER (PARTITION BY d.department_name))/ (SUM (e.salary) OVER ()) * 100 salary_pct

  FROM employees e, departments d

 WHERE e.department_id = d.department_id;

Method #3

  SELECT d.department_name,

         100 * SUM (e.salary) / (SUM (SUM (e.salary)) OVER ()) AS salary_pct

    FROM employees e, departments d

   WHERE e.department_id = d.department_id

GROUP BY d.department_name;

Method #4

WITH t AS (SELECT SUM (salary) AS sum_salary FROM employees)

  SELECT d.department_name, SUM (e.salary) / sum_salary * 100 AS salary_pct

    FROM employees e, departments d, t

   WHERE e.department_id = d.department_id

GROUP BY d.department_name, sum_salary;