I need to calculate percentage of hours per each project, not out of all the quantity of projects.
Here is the initial table:
| employee_id | project_id | hours |
|---|---|---|
| 999111111 | 1 | 31.4 |
| 999111111 | 2 | 8.5 |
| 999333333 | 3 | 42.1 |
| 999888888 | 1 | 21.0 |
| 999888888 | 2 | 22.0 |
| 999444444 | 2 | 12.2 |
| 999444444 | 3 | 10.5 |
| 999444444 | 1 | null |
| 999444444 | 10 | 10.1 |
| 999444444 | 20 | 11.8 |
| 999887777 | 30 | 30.8 |
| 999887777 | 10 | 10.2 |
| 999222222 | 10 | 34.5 |
| 999222222 | 30 | 5.1 |
| 999555555 | 30 | 19.2 |
| 999555555 | 20 | 14.8 |
| 999666666 | 20 | null |
Needed output:
| employee_id | project_id | percent |
|---|---|---|
| 999111111 | 1 | 60 |
| 999111111 | 2 | 20 |
| 999333333 | 3 | 80 |
| 999888888 | 1 | 40 |
| 999888888 | 2 | 52 |
| 999444444 | 2 | 29 |
| 999444444 | 3 | 20 |
| 999444444 | 1 | null |
| 999444444 | 10 | 18 |
| 999444444 | 20 | 44 |
| 999887777 | 30 | 56 |
| 999887777 | 10 | 19 |
| 999222222 | 10 | 63 |
| 999222222 | 30 | 9 |
| 999555555 | 30 | 35 |
| 999555555 | 20 | 56 |
| 999666666 | 20 | null |
I understand how to calculate out of overall COUNT of all hours, but I need percentage per employee out of COUNT of hours within the same project ID, and that's what I'm struggling with. How can it be done?
CodePudding user response：
Assuming every project would have at least one record with a non zero hours value, we can try using this query:
SELECT employee_id, project_id,
100.0 * hours / SUM(hours) OVER (PARTITION BY project_id) AS percent
FROM yourTable
ORDER BY project_id, employee_id;
CodePudding user response：
You can use window functions to calculate the percentage of hours for each project:
SELECT employee_id, project_id,
100.0 * SUM(hours) OVER (PARTITION BY project_id) / SUM(hours) OVER () AS percent
FROM yourTable
ORDER BY employee_id, project_id;