Uso de Funciones de Grupo (PL/SQL)

Funciones de Grupos o Agregadas

• AVG: calcula el promedio del valor de n, ignorando los valores Nulos.
• COUNT: cuenta el número de filas, donde expr puede ser el nombre de una columna.
• MAX: obtiene el máximo valor de expr, ignorando los valores Nulos.
• MIN: obtiene el mínimo valor de expr, ignorando los valores Nulos.
• SUM: suma los valores de expr, ignorando los valores Nulos.
• STDDEV: determina la desviación estándar de n, ignorando los valores Nulos.
• VARIANCE:  devuelve la varianza de un conjunto de número.
• La opción DISTINCT hace que la función considere sólo los valores no duplicados.
• La opción ALL (valor por defecto) hace que la función considere todos los valores incluyendo los duplicados.
• El tipo de datos para expr debe ser CHAR, VARCHAR2, NUMBER, DATE.
• Todas las funciones de grupo ignoran los valores Nulos. Para sustituir los valores Nulos se deben utilizar las funciones NVL, NVL2 ó COALESCE.

-- Funciones AVG, SUM, MAX, MIN

SELECT ROUND(AVG(salary)) "Salario Promedio", MAX(salary) "Salario Máximo",
       MIN(salary) "Salario Mínimo", SUM(salary) "Sumatoria de los Salarios"
FROM employees
WHERE job_id LIKE '%REP%';

SELECT MIN(hire_date), MAX(hire_date), MIN(last_name),   MAX(last_name)
FROM employees;

En el primer ejemplo, la sentencia obtiene el salario promedio, el salario mayor, el salario menor y la sumatoria de todos los salarios de los empleados cuya identificación de trabajo contenga la cadena de caracteres REP independiente con la cantidad de caracteres que comience o termine.

En el segundo ejemplo, la sentencia muestra la fecha de contrato menor, la fecha de contrato mayor, el primer apellido y el último apellidos de los empleados.

Uso de Función COUNT

La función COUNT tiene tres formatos:

• COUNT(*): retorna el número de filas que satisfacen el criterio de la sentencia SELECT incluyendo filas duplicadas y filas que contengan valores Nulos en cualquiera de sus columnas. Si se
  incluye las cláusula WHERE en la sentencia SELECT , COUNT(*) retorna el número de filas que satisfacen o cumplen con la condición en la cláusula WHERE.
• COUNT(expr): retorna el número de filas que poseen valores NO Nulos en la columna identificada por expr.
• COUNT(DISTINCT expr): retorna el número de filas que contengan valores diferentes No Nulos en la columna identificada por expr.

-- Funcion COUNT

SELECT COUNT(*) "Total Empleados", COUNT(commission_pct) "Empleados con Comisión"
FROM employees
WHERE department_id = 50;

SELECT COUNT(manager_id) "Empleados que poseen Jefe", 
       COUNT(DISTINCT department_id) "Deptos. Diferentes"
FROM employees;

En el primer ejemplo, la sentencia retorna el total de empleados o número de filas que pertenecen al departamento 50. Además obtiene el número de filas que poseen un valor NO Nulo en la columna commission_pct (o que poseen porcentaje de comisión) para los empleados que trabajan en el departamento 50.

En el segundo ejemplo, la sentencia retorna la número de filas que poseen un valor NO Nulo en la columna manager, es decir los empleados que poseen jefe. Además muestra cuántos departamentos diferentes existen en la tabla EMPLOYEES.

Uso de Función de Grupo con valores Nulos

Las funciones de grupo ignoran los valores nulos en las columnas excepto la función COUNT en su formato COUNT(*). Sin embargo, la función NVL fuerza a las funciones de grupo a incluir los valores nulos

-- Funciones con valores nulos

SELECT AVG(commission_pct) "PROMEDIO SIN CONSIDERAR FILAS CON NULOS"
FROM employees;

SELECT AVG(NVL(commission_pct,0)) "PROMEDIO CONSIDERANDO NULOS"
FROM employees;

En la primera sentencia, la función de AVG obtiene la comisión promedio pero considerando solo las filas que poseen valor en la columna commission_pct. Para que sean consideras las 107 filas de la tabla EMPLOYEES se debe usar la función NVL. En la segunda sentencia, la función NVL reemplazará con cero cuando la columna commission_pct tenga un valor Nulo. Una vez efectuado esto, la función AVG obtiene el promedio pero ahora considerando todas las filas de la tabla EMPLOYEES.

Creando Grupos de Datos

Se puede usar la cláusula GROUP BY para dividir las filas de la tabla en grupos más pequeños y obtener valores por cada grupo utilizando las funciones de grupos.

Si en una cláusula SELECT se incluye una función de grupo y además se selecciona una columna individual, ésta se DEBE incluir en la cláusula GROUP BY. Si no se incluye dicha columna en la cláusula GROUP BY se produce el error ORA-00937.

Usando la cláusula WHERE se pueden excluir filas antes de que se dividan en grupos.

Todas las columnas que no están incluidas en las funciones de grupos se deben incluir en la cláusula GROUP BY.

No se pueden utilizar columnas alias en la cláusula GROUP BY.

Las columnas de la cláusula GROUP BY no es necesario que estén en la cláusula SELECT, sin embargo esto hace que la información que se entrega no sea muy clara.

-- Ejemplo

SELECT department_id departamento,  
       ROUND(AVG(salary)) "SALARIO_PROMEDIO X DEPTO"
FROM employees
GROUP BY department_id
ORDER BY "SALARIO_PROMEDIO X DEPTO";

En la sentencia del ejemplo, se muestra por cada departamentos el salario promedio. La información se visualiza ordenada en forma ascendente por el salario promedio. A diferencia de la cláusula GROUP BY, en la cláusula ORDER BY puede ir el alias asignado a la columna de salida o puede ir la expresión ROUND(AVG)salary)).

-- Ejemplo

SELECT job_id "TRABAJO", ROUND(AVG(salary)) "SALARIO PROMEDIO X TRABAJO"
FROM employees
WHERE job_id LIKE '%A%'
GROUP BY job_id
ORDER BY job_id;

En la sentencia del ejemplo, se muestra la identificación del trabajo y su salario promedio. Esta información se muestra por cada trabajo que contenga la letra A.

Creando Grupos de Datos por varias Columnas

Las funciones de grupos pueden tratar a toda la tabla como un solo gran grupo, pero existe el caso en que se hace necesario “dividir” la información existente en la tabla en grupos más pequeños utilizando la cláusula GROUP BY.

Las filas de la tabla EMPLOYEES primero se agrupan por departamento, después por cada departamento se agrupan por trabajo para finalmente sumar los salarios de cada trabajo existentes en cada departamento.

-- Ejemplo

SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id
ORDER BY department_id, job_id;

En la sentencia del ejemplo, en primer lugar , las filas se agrupan por identificación de departamento. En segundo lugar, las filas se agrupan por identificación de trabajo en los grupos de departamento. La cláusula ORDER BY ordena los resultados por identificación de departamento y por cada departamento ordena por identificación de trabajo. La función SUM se aplica a la columna de salario para todos los trabajos en cada grupo de departamentos

-- Sentencia Errónea

SELECT department_id, COUNT(last_name)
FROM employees;

-- Sentencia Correcta

SELECT department_id, COUNT(last_name)
FROM employees
GROUP BY department_id;

Cualquier columna o expresión especificada en la cláusula SELECT que no es una función de grupo debe estar en la cláusula GROUP BY, de lo contrario se producirá el error oracle ORA-00937 como se muestra en a sentencia errónea. La solución para corregir este error, es que la columna department_id se debe incorporar a la cláusula GROUP BY.

-- Sentencia Errónea

SELECT department_id, MAX(salary)
FROM employees
WHERE MAX(salary) > 8000
GROUP BY department_id;

En la cláusula WHERE NO se puede utilizar funciones de grupos para restringir los grupos de filas a mostrar. En el ejemplo, se desea mostrar solo la información de los departamentos con salario máximo mayor a 8000, pero como no se puede utilizar funciones de grupo en la cláusula WHERE se genera el error oracle ORA-00934.

Restricciones de Resultados del Grupo

Para restringir las filas que se desean seleccionar se utiliza la cláusula WHERE, pero para restringir grupos de filas basado en el resultado de una función de grupo se debe utilizar la cláusula HAVING.

Cuando se usa la cláusula HAVING, el servidor Oracle restringe los grupos en el siguiente orden:
1.- Las filas son agrupadas
2.- La función de grupo es aplicada al grupo.
3.- Se muestran los grupos que coinciden con el criterio de la cláusula HAVING.

Los grupos son formados y las funciones de grupos con calculadas antes de que la cláusula HAVING se aplique a los grupos.

Para encontrar el máximo salario en cada departamento pero que sea mayor a 8000 se debe hacer lo siguiente:
Encontrar el salario máximo por cada departamento agrupando por número de departamento.
Restringir los grupos para los departamentos con un máximo salario mayor a 8000.

-- Ejemplo

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 8000
ORDER BY department_id;

La sentencia del ejemplo muestra los departamentos y los salarios máximos de aquellos que cumplan con la condición de grupo que indica que el salario máximo debe ser mayor a 80000 para que el grupo sea retornado por la sentencia SELECT.

-- Ejemplo

SELECT department_id, ROUND(AVG(salary))
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 1000
ORDER BY ROUND(AVG(salary)) DESC;

En el ejemplo, la sentencia muestra el departamento y el salario promedio (redondeado) de los departamentos que posean con un salario máximo mayor a 10000.

-- Ejemplo

SELECT department_id, MIN(salary), MAX(salary)
FROM employees
HAVING MIN(salary) > 2500
AND MAX(salary) > 5000
GROUP BY department_id;

SELECT department_id, MIN(salary), MAX(salary)
FROM employees
WHERE department_id > 20
HAVING MIN(salary) > 2500
AND MAX(salary) > 5000
GROUP BY department_id;

En la primera sentencia se muestran los departamentos, sus salarios mínimos y máximos de aquellos que posean un salario mínimo mayor a 2500 y un salario máximo mayor a 5000.

En la segunda sentencia se muestran los departamentos, sus salarios mínimos y máximos de aquellos que cumplan con las siguientes tres condiciones:

• La identificación del departamento debe ser mayor a 20.
• Deben poseer un salario mínimo mayor a 2500
• y deben poseer un salario máximo mayor a 5000,

En el resultado por lo tanto no se muestra el departamento 20 y tampoco el departamento con identificación NULL.

-- Ejemplo

SELECT ROUND(MAX(AVG(salary))) "MÁXIMO SALARIO PROMEDIO"
FROM employees
GROUP BY department_id;

En la sentencia del ejemplo, primero se obtiene el salario por cada departamento, después entre todos los salarios promedios de obtiene el máximo para finalmente mostrar ese valor redondeado.

Guía:

/* 1.- El departamento de Finanzas desea conocer cuál es la realidad de la empresa en relación a los salarios que hasta la fecha perciben los 
empleados. Para ello, se desea que Ud. genere un informe con el salario mínimo, salario máximo, valor total de los salarios y salario promedio 
entre todos los empleados. La información se debe mostrar en el formato como se muestra en el ejemplo y con los valores redondeados: */
SELECT ROUND(MIN(SALARY)) "SALARIO MINIMO", ROUND(MAX(SALARY)) "SALARIO MAXIMO",
       ROUND(SUM(SALARY)) "VALOR TOTAL SALARIOS", ROUND(AVG(SALARY)) "SALARIO PROMEDIO"
FROM EMPLOYEES;

/* 2.- Se desea evaluar el aumentar los salarios de acuerdo a los trabajos que se desempeñan en la actualidad en la empresa. Por esta razón, se 
requiere que Ud. genere un reporte que por cada trabajo se muestre el salario máximo, salario mínimo, valor total de los salarios y salario 
promedio. La información se debe mostrar en el formato como se muestra en el ejemplo, con los valores redondeados y ordenada en forma ascendente 
por trabajo: */
SELECT JOB_ID trabajo, ROUND(MAX(SALARY)) "SALARIO MAXIMO", ROUND(MIN(SALARY)) "SALARIO MINIMO", 
       ROUND(SUM(SALARY)) "VALOR TOTAL SALARIOS", ROUND(AVG(SALARY)) "SALARIO PROMEDIO"
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID;

/* 3.- La cadena de supermercados SANTA CHABELA posee un Sistema Informático que gestiona la información de  sus  empleados. Debido a que aún 
posee algunas falencias en la entrega de información, se requiere que Ud. genere una serie de informes para satisfacer los requerimientos de 
información planteados en cada caso. La información que se debe generar en el formato de ejemplo de cada requerimiento:
  a) Total de empleados por cada trabajo: */
SELECT JOB_ID trabajo, COUNT(JOB_ID) "TOTAL EMPLEADOS" 
FROM EMPLOYEES
GROUP BY JOB_ID; 
  
  /* b)  Total de empleados que tiene a su cargo cada jefe. Mostrar la información ordenado en forma descendente por el total de empleados y 
  ascendente por jefe: */
SELECT MANAGER_ID jefe, COUNT(EMPLOYEE_ID) "TOTAL DE EMPLEADOS A SU CARGO"
FROM EMPLOYEES
GROUP BY MANAGER_ID
ORDER BY "TOTAL DE EMPLEADOS A SU CARGO" DESC, MANAGER_ID;

  /* c)  Cuadro Resumen del total de empleados y total de salarios por cada departamento. El valor del salario total por departamento debe 
  aparecer con el símbolo punto como separador de miles: */
SELECT NVL(TO_CHAR(DEPARTMENT_ID),' ') departamento, COUNT(EMPLOYEE_ID) "TOTAL DE EMPLEADOS", 
       TO_CHAR(SUM(SALARY),'$999G999') "SALARIO TOTAL X DEPTO" 
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID;

  /* d) Diferencia que existe entre el salario mayor y menor que perciben los empleados: */
SELECT TO_CHAR(MAX(SALARY) - MIN(SALARY),'$99,999') diferencia 
FROM EMPLOYEES;

  /* e) Cuántos jefes diferentes existen en la empresa: */
SELECT COUNT(DISTINCT(NVL(TO_CHAR(MANAGER_ID),' '))) "Total de Jefes" FROM EMPLOYEES;

  /* f) Departamentos en los que trabajan más de 5 empleados: */
SELECT DEPARTMENT_ID DEPARTAMENTO, COUNT(EMPLOYEE_ID) "TOTAL DE EMPLEADOS" 
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(EMPLOYEE_ID) > 5;

  /* g)  Por cada trabajo que se realiza en cada departamento mostrar el salario mínimo que se paga para ese trabajo en ese departamento. 
  Excluir los departamentos nulos y los grupos donde el salario mínimo es menor o igual a 6000. Ordenar los datos de salida en forma ascendente  
  por departamento y trabajo. */
SELECT DEPARTMENT_ID departamento, JOB_ID trabajo, MIN(SALARY) "SALARIO MINIMO"
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID, JOB_ID
HAVING MIN(SALARY) > 6000
ORDER BY DEPARTMENT_ID, JOB_ID;

/* 4.- Después de una reunión efectuada entre la Gerencia y el departamento de Informática de la Cadena del Supermercado, Ud. deberá dar solución 
con urgencia a los siguientes requerimientos:
  a) Se han detectado algunas irregularidades en el gasto de pagos de salarios de algunos departamentos versus la cantidad de empleados que 
  trabajan en ellos. Por esta razón, se debe emitir un informe con los departamentos en donde el gasto total por pago de salarios está entre los 
  $15000 y los $30000. La información se debe mostrar según el ejemplo y ordenada por el valor total del salario por departamento en forma ascendente: */
SELECT DEPARTMENT_ID departamento, COUNT(EMPLOYEE_ID) "TOTAL EMPLEADOS",
       TO_CHAR(SUM(SALARY),'$99,999') "TOTAL SALARIO X DEPTO"
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING SUM(SALARY) BETWEEN 15000 AND 30000
ORDER BY SUM(SALARY);

  /* b) Se desea evaluar la posibilidad de pagarle a cada jefe una comisión basada en el salario máximo del empleado del cual es jefe y el número total 
  de empleados a su cargo. Así por ejemplo si el jefe 100 posee 5 empleados a su cargo la comisión que le corresponderá será el 5% del salario máximo 
  entre sus 5 empleados, si el jefe 201 posee 3 empleados su comisión corresponderá al 3% del salario máximo entre los 3 empleados a su cargo, etc. 
  La información que se solicitada es la que se muestra en el ejemplo y ordena en forma ascendente por jefe: */
SELECT NVL(TO_CHAR(MANAGER_ID),' ') jefe, COUNT(EMPLOYEE_ID) "TOTAL DE EMPLEADOS A CARGO", 
       MAX(SALARY) "SALARIO MAXIMO", ROUND(MAX(SALARY) * COUNT(EMPLOYEE_ID) / 100) comision
FROM EMPLOYEES
GROUP BY MANAGER_ID
ORDER BY MANAGER_ID;

  /* c) Se está efectuando una auditoría por irregularidades de salarios que se están pagando en los departamentos 20, 50, 80 y 90. Por lo tanto 
  los auditores requieren de un informe que muestre el valor total de salario que se paga por cada trabajo en cada uno de estos departamentos. 
  La información que se requiere es la que se muestra en el ejemplo en el formato que se muestra: */
SELECT JOB_ID trabajo, 
       NVL(TO_CHAR(CASE DEPARTMENT_ID WHEN 20 THEN SUM(SALARY) END,'$999,999'),' ') depto_20,
       NVL(TO_CHAR(CASE DEPARTMENT_ID WHEN 50 THEN SUM(SALARY) END,'$999,999'),' ') depto_50,
       NVL(TO_CHAR(CASE DEPARTMENT_ID WHEN 80 THEN SUM(SALARY) END,'$999,999'),' ') depto_80,
       NVL(TO_CHAR(CASE DEPARTMENT_ID WHEN 90 THEN SUM(SALARY) END,'$999,999'),' ') depto_90
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (20,50,80,90)
GROUP BY JOB_ID,DEPARTMENT_ID;