how to get percentage out of 100 for salary

Question

Im using sql developer and i want to:

• add total of salary to the last row
  

• percentage in a new column, it should shows the percentage for each an employee out of 100%

How to do it ?

select name ,sal from(
select last_name name,salary sal from employees e
where  rownum <=5 
)

wanted output to be :

Answer 1

You can do this using grouping set, but it is a little bit tricky:

select name, salary, salary / max(salary) over ()
from t
group by grouping sets ((name, salary) ());

The trick is that the window function is calculated after the grouping sets, so it includes the total. Hence the use of max(salary) instead of sum(salary).

Answer 2

You can use window functions to compute the salary ratio (or percentage), and union all an aggregate query to generate the grand total:

select last_name, salary, salary / sum(salary) over() salary_ratio from employee
union all
select null, sum(salary), 1 from employee

This gives you ratios, that is values between 0 and 1. If you want percentages, then you can just multiply by 100.

Answer 3

You can use the analytic function RATIO_TO_REPORT to get the percentage that the salary is of the total salaries. You can add rounding to the last column if you'd like to see less decimal places.

Query

WITH
    employees (name, sal)
    AS
        (SELECT 'King', 24000 FROM DUAL
         UNION ALL
         SELECT 'Kochhar', 17000 FROM DUAL
         UNION ALL
         SELECT 'De Haan', 17000 FROM DUAL
         UNION ALL
         SELECT 'Hunold', 9000 FROM DUAL
         UNION ALL
         SELECT 'Ernst', 6000 FROM DUAL)
SELECT name, sal, (ratio_to_report (sal) OVER () * 100) AS percent
  FROM employees;

Result

      NAME      SAL                                      PERCENT
__________ ________ ____________________________________________
King          24000    32.87671232876712328767123287671232876712
Kochhar       17000    23.28767123287671232876712328767123287671
De Haan       17000    23.28767123287671232876712328767123287671
Hunold         9000    12.32876712328767123287671232876712328767
Ernst          6000     8.21917808219178082191780821917808219178