select with groupings

2

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
``````
asked by luis javier 13.02.2018 в 15:37
source

1

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` .

source
0

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```