Can be obtained in a single select ???
An example company, department, and production carried out
Obtain the percentage of production: the production of the person between the production of his department of his department (production person * 100 / production department
Department production:
select departamento, sum(produción)
from empresa
group by departamento
Worker production
select dni, departamento,sum(produción)
from empresa
group by dni,departamento
The query you are looking for is the following:
SELECT
dni, producción, producción,
producción * 100 / suma porcentaje,
emf.departamento, suma
FROM empresa emf
INNER JOIN (
SELECT
SUM(producción) suma,
departamento
FROM empresa
GROUP BY departamento
) su
ON su.departamento = emf.departamento
You can see the operation online at this link and it is divided into two parts.
The first part (the inner query) gets the sum of each department and the result table calls it su (the field suma would be accessed as su.suma if there were name conflicts):
SELECT
SUM(producción) suma,
emj.departamento
FROM empresa
GROUP BY departamento
The second part (the external query) relates each employee in the empresa table to the total results obtained by the previous query, so that each record obtained will have the department's total in the suma field, being able to calculate producción * 100 / suma without problems in the field porc .
Looking for other pages of plsql I have found another solution, but I am left with the question of which is more optimal
SELECT emf.dni
emf.producción,
emf.producción *100 / SUM( emf.producción ) OVER( PARTITION BY emf.departamento ) porcentaje,
emf.departamento,
SUM( emf.producción ) OVER( PARTITION BY emf.departamento ) suma
FROM empresa emf
ORDER BY dni