CODEWITHSUNDEEP
sqloracle-databasepercentage
thenut
thenut

Reputation: 11

Calculate percentage rows over some specific value

Is there any oracle function which will help me to calculate percentage of rows which accepts some condition

Example Table:

   WorkerId   Salary    DepartmentId

   10001       2000.00      1
   10002       2500.00      2
   10004       3000.00      1
   10005       3500.00      1

I would like to know what is the percentage of workers which have salary over 2100.00 per each Department

Upvotes: 1

Views: 837

Answers (3)

Boneist
Boneist

Reputation: 23578

You can do this without needing to use an analytic function, like so:

WITH sample_data AS (SELECT 10001 workerid, 2000 salary, 1 departmentid FROM dual UNION ALL
                     SELECT 10002 workerid, 2500 salary, 2 departmentid FROM dual UNION ALL
                     SELECT 10004 workerid, 3000 salary, 1 departmentid FROM dual UNION ALL
                     SELECT 10005 workerid, 3500 salary, 1 departmentid FROM dual)
-- end of subquery mimicking a table with your data in it.
-- see SQL query below:
SELECT departmentid,
       100*(COUNT(CASE WHEN salary > 2100 THEN 1 END)/COUNT(*)) pct_earning_gt_2100
FROM   sample_data
GROUP BY departmentid;

DEPARTMENTID PCT_EARNING_GT_2100
------------ -------------------
           1    66.6666666666667
           2                 100

This uses a conditional count (remember, null values are ignored by most aggregate functions) to decide how many rows meet the condition, before dividing by the total number of rows, per department.

This should be more performant than the solution involving the ratio_to_report analytic function, since it doesn't need to do that extra step before doing the group by, but you should test both solutions to find out which one is actually better with your data.

Upvotes: 0

Radim Bača
Radim Bača

Reputation: 10701

Try using two GROUP BY and JOIN

SELECT yt.DepartmentId, t.hs/count(*) * 100
FROM your_tab yt
JOIN 
(
   SELECT DepartmentId, count(*) as hs
   FROM your_tab
   WHERE salary > 2100
   GROUP BY DepartmentId
) t ON yt.DepartmentId = t.DepartmentId
GROUP BY yt.DepartmentId, t.hs

dbfiddle

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You could use RATIO_TO_REPORT:

SELECT departmentID, 100 * SUM(rr) AS total_percentage
FROM  (SELECT t.*, RATIO_TO_REPORT(1) OVER (PARTITION BY DepartmentId) AS rr
       FROM your_tab t) s
WHERE salary > 2100
GROUP BY departmentId;

DBFiddle Demo

Output:

DEPARTMENTID TOTAL_PERCENTAGE
1            66.66
2            100

Upvotes: 3

Related Questions