Home / Questions / 138170
Accepted
luis javier
Asked: 2020-02-14 06:37:36 +0800 CST 2020-02-14 06:37:36 +0800 CST

select with groupings

  • 772

It can be obtained in a single select???

An example company ID, 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
plsql

2 Answers

  1. Best Answer

    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 how it works online at this link and it is divided into two parts.

    The first part (the inner query) obtains the sum of each department and calls the result table su(the field sumawould be accessed as su.sumaif there were a name conflict):

    SELECT
  SUM(producción) suma,
  emj.departamento
FROM empresa
GROUP BY departamento

    The second part (the external query) relates each employee in the table empresawith the total results obtained by the previous query, so that each record obtained will have the total of the department associated in the field suma, being able to calculate producción * 100 / sumawithout problems in the field porc.

    • 1

  2. Searching for other plsql pages I have found another solution, but I have the doubt of which one is more optimal

    SELECT    emf.dni
         emf.production,
         emf.production *100 / SUM( emf.production ) OVER( PARTITION BY emf.department ) percentage,
         emf.department,
         SUM( emf.production ) OVER( PARTITION BY emf.department ) sum
FROM      emf company
 ORDER BY dni

    • 0