Objetivos:
•Identificar las funciones de grupo disponibles.•Describir el uso de las funciones de grupo.
•Agrupar datos mediante la cláusula GROUP BY.
•Incluir o excluir filas agrupadas utilizando la cláusula HAVING.
NOTA: Usamos como ejemplo la base de datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.
_____________________________________________________________________________________
Funciones de Grupo.
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
A diferencia de las funciones de una sola fila, las funciones de grupo operan en juegos de filas para dar un resultado por grupo. Estos juegos pueden abarcar toda la tabla o la tabla dividida en grupos.
Sintaxis
SELECT [ column,] group_function( [DISTINCT] column)[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
Descripción:
•A diferencia de las funciones de
una sola fila, las funciones de grupo operan en juegos de filas para dar un
resultado por grupo. Estos juegos pueden abarcar toda la tabla o la tabla
dividida en grupos.
•Todas las funciones de grupo ignoran los valores nulos de la columna, pero con el uso de las funciones NVL/NVL2/COALESCE puedes forzar a las funciones de grupo a incluir valores nulos.
•Utilice la palabra clave DISTINCT column para suprimir el recuento de valores
duplicados en una columna.
•En la Sintaxis GROUP BY es usado para dividir las filas de una tabla en grupos, group_by_expression especifica columnas cuyos valores
determinan la base para agrupar filas.
•Utilize HAVING para restringir los grupos a mostrar. HAVING es equivalente a WHERE, con la diferencia que HAVING es para restringir grupos, mientras que WHERE restringe filas de datos. group_condition es la condición de restricción aplicada a los grupos.
_____________________________________________________________________________________
Algunas Funciones de Grupo.
Cada una de las funciones acepta un argumento. A continuación mostramos el uso de algunas de ellas:
•AVG([DISTINCT | ALL] expr): Retorna el valor medio o promedio de expr. Omite los valores nulos.
•COUNT({ * | [DISTINCT | ALL] expr}): Retorna el Número de filas, en las que expr se evalúa como algo no nulo ( si se usa * cuenta todas las filas de la tabla, incluidas las duplicadas y las que contienen valores nulos).
•MAX([DISTINCT | ALL] expr): Retorna el Valor máximo de expr; se ignoran los valores nulos.
MIN([DISTINCT | ALL] expr): Retorna el Valor mínimo de expr; se ignoran los valores nulos.
•STDDEV([DISTINCT | ALL] expr):Retorna la Desviación
estándar de expr; se ignoran los
valores nulos.
•SUM([DISTINCT | ALL] expr): Retorna la sumatoria total de expr. se omiten los valores nulos.
•VARIANCE([DISTINCT | ALL] expr): Retorna la varianza o cuadrado de la desviación estándar de expr. Omite los valores nulos.
Otros Complementos.
•DISTINCT hace que la función considere únicamente valores no duplicados; ALL hace que considere todos los valores, incluidos los duplicados. El valor por defecto es ALL y, por tanto, no es necesario especificarlo.
•Los tipos de datos para las funciones con un argumento expr pueden ser CHAR, VARCHAR2, NUMBER o DATE.
•Todas las funciones de grupo ignoran los valores nulos. Para sustituir con un valor los valores nulos, utilice las funciones NVL, NVL2 o COALESCE.
•Al usar GROUP BY, Es posible agrupar por mas de una Columna.
•Al igual que las funciones de fila única, es posible anidar las funciones de Grupo.
_____________________________________________________________________________________
Uso de las Funciones de Grupo.
El Fin de estas funciones es el de obtener información de resumen (como por ejemplo, medias, cantidad, etc...) para grupos de filas.
Ejemplos:
SELECT
COUNT(salary), MAX(salary),
MIN(salary), SUM(salary),
AVG(salary), STDDEV(salary),
VARIANCE(salary)
FROM hr.employees;
/*En el ejemplo anterior aplicamos las funciones de grupo ya explicadas al campo salary de la tabla employees*/
---
SELECT
COUNT(DISTINCT salary), COUNT(salary),
AVG(NVL(COMMISSION_PCT,0)),AVG(COMMISSION_PCT),
MIN(last_name), MAX(last_name)
FROM hr.employees;
/*En el anterior ejemplo vemos el uso de DISTINCT y la función NVL en conjunto con las funciones de grupo, la 1ra columna muestra la cantidad de salarios distintos en toda la tabla; la 2da muestra la cantidad de empleados con salarios; la 3ra muestra el promedio de COMMISSION_PCT tomando en cuenta la cantidad de empleados total, la 4ta muestra el promedio tomando en cuenta solo los empleados que ganan comisión; la 5ta muestra el uso de la función MIN con campos tipo carácter y la 6ta el uso de MAX también en campos tipo carácter.*/
---
SELECT
COUNT(*), COUNT(COMMISSION_PCT),
MIN(hire_date), MAX(hire_date)
FROM employees;
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id;
SELECT
COUNT(*), COUNT(COMMISSION_PCT),
MIN(hire_date), MAX(hire_date)
FROM employees;
/*En el Query anterior en la columna 1 se usa la función de grupo COUNT(*), para determinar la cantidad total de registros de la tabla Employees; columna 2 muestra la cantidad de empleados con Comisión Salariar; la 3ra columna muestra la fecha de contratación del empleado mas viejo de la empresa; la 4ra muestra la fecha de contratación del empleado mas reciente de la empresa.*/
---
SELECT department_id AS "Departamento",
COUNT(*) AS "Cantidad Empleado",
MIN(salary) AS "Salario Mínimo",
AVG(salary) AS "Salario Promedio",
MAX(salary) AS "Salario Máximo"
FROM employees
GROUP BY department_id
ORDER BY 2 desc;
/*En el Query anterior la 1ra columna muestra el numero de departamento; la 2da muestra la cantidad de empleados de ese departamento; la 3ra muestra el salario mínimo de ese departamento; la 4ta muestra el salario promedio y la 5ta muestra el salario máximo.
Nota: La columna(department_id) en la cláusula GROUP BY no tiene que estar necesariamente en la cláusula SELECT. Sin embargo, sin los números de departamento,
los resultados no parecen significativos. */
---
Grupos Dentro de Grupos.
En ocasiones, necesita ver los resultados para grupos dentro de grupos. A continuación se muestra un informe que detalla el salario total que se paga a cada cargo en cada departamento.
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id;
/*La tabla EMPLOYEES se agrupa primero por número de departamento y después por cargo dentro de esa agrupación. Por ejemplo, los cuatro administrativos del departamento 50 se agrupan juntos y se crea un único resultado (salario total) para todos los administrativos del grupo.*/
•La cláusula GROUP BY especifica cómo se deben agrupar las filas:
-En primer lugar, las filas se agrupan por número de departamento.
-En segundo lugar, las filas se agrupan por identificador de trabajo de los grupos de número de departamento.
Así pues, la función SUM se aplica a la columna de salarios para todos los identificadores de trabajo de cada grupo de números de departamento.
De la misma forma que utiliza la cláusula WHERE para restringir las filas que se seleccionarán, utilice la cláusula HAVING para restringir grupos. Para buscar el salario máximo de cada departamento que tenga un salario máximo superior a 10.000 dólares, necesita:
1. Buscar el salario máximo de cada departamento agrupando por número de departamento.
2. Restringir los grupos a los departamentos con un salario máximo superior a 10.000 dólares.
Ejemplo:
SELECT job_id, SUM(salary) AS NÓMINA FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);
/*El ejemplo anterior muestra el identificador de trabajo y el salario mensual total de cada trabajo
que tiene una nómina total superior a 13000. El ejemplo excluye a los
representantes de venta y ordena la lista por salario mensual total.*/
---
Anidando Funciones de Grupo.
Las funciones de grupo se pueden anidar hasta una profundidad de dos. El ejemplo a continuación muestra el salario medio máximo.
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id ;
FROM employees
GROUP BY department_id ;
_____________________________________________________________________________________
_____________________________________________________________________________________
Fuente: Base de Datos Oracle 10g: Conceptos Fundamentales de SQL 1
