Agregando Datos Usando Funciones de Grupo Copyright © EDCON Tecnologías de Información, 2008. Objetivos Al completar esta lección podrá realizar lo siguiente: • • • • Identificar las funciones de grupo disponibles Describir el uso de funciones de grupo Agrupar datos utilizando la clausula GROUP BY Incluir o excluir registros agrupados usando la cláusula HAVING 4-2 ¿Qué son las funciones de grupo? Las funciones de grupo trabajan en colecciones de registros para brindar un resultado por grupo. EMPLOYEES El salario máximo en la tabla EMPLOYEES. … 4-3 Tipos de funciones de grupo • • • • • • • AVG COUNT MAX Función de Grupo MIN STDDEV SUM VARIANCE 4-4 Sintaxis de Funciones de grupo SELECT FROM [WHERE [GROUP BY [ORDER BY [column,] group_function(column), ... table condition] column] column]; 4-5 Usando las funciones AVG y SUM Se puede usar AVG y SUM para datos numéricos. SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%'; 4-6 Usando las funciones MIN y MAX Se puede usar MIN y MAX para cualquier tipo de datos. SELECT MIN(hire_date), MAX(hire_date) FROM employees; 4-7 Usando la función COUNT COUNT(*) retorna el número de registros en la tabla. SELECT COUNT(*) FROM employees WHERE department_id = 50; 4-8 Usando la función COUNT • COUNT(expr) retorna el numero de registros con valores no-nulos para la expresión expr. • Despliega el número de valores de departamentos en la tabla de EMPLOYEES, excluyendo los valores nulos. SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80; 4-9 Usando la palabra reservada DISTINCT • • COUNT(DISTINCT expr) retorna el número de valores distintos no-nulos para la expresión expr. Despliega el número de valores de departamentos distintos en la tabla EMPLOYEES. SELECT COUNT(DISTINCT department_id) FROM employees; 4-10 Funciones de Grupo y Valores Nulos Las funciones de Grupo ignoran los valores nulos de la columna. SELECT AVG(commission_pct) FROM employees; 4-11 Usando la función NVL con Funciones de Grupo La función NVL obliga a las funciones de grupo a incluir los valores nulos. SELECT AVG(NVL(commission_pct, 0)) FROM employees; 4-12 Creando Grupos de Datos EMPLOYEES 4400 9500 El salario 3500 promedio en la tabla 6400 EMPLOYEES para cada departamento. 10033 … 4-13 Creando Grupos de datos: Sintaxis de la cláusula GROUP BY SELECT FROM [WHERE [GROUP BY [ORDER BY column, group_function(column) table condition] group_by_expression] column]; Divide los registros en una tabla dentro de grupos pequeños usando la cláusula GROUP BY. 4-14 Usando la cláusula GROUP BY Todas las columnas en la lista del SELECT que no están en funciones de grupo deben estar en la cláusula GROUP BY. SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ; 4-15 Usando la cláusula GROUP BY La columna GROUP BY no debe estar en la lista del SELECT. SELECT AVG(salary) FROM employees GROUP BY department_id ; 4-16 Agrupando mas de una columna EMPLOYEES … “Sumar los sueldos de la tabla EMPLOYEES para cada trabajo, agrupado por departamento. 4-17 Usando la cláusula GROUP BY en Múltiples Columnas SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ; 4-18 Consultas Ilegales Usando Funciones de Grupo Cualquier columna o expresión en la lista de SELECT que no sea una función agregada debe de estar en la cláusula GROUP BY. SELECT department_id, COUNT(last_name) FROM employees; SELECT department_id, COUNT(last_name) * ERROR at line 1: ORA-00937: not a single-group group function 4-19 Consultas Ilegales Usando Funciones de Grupo • No se puede usar la cláusula WHERE para restringir grupos. • Se puede usar la cláusula HAVING para restringir grupos. • No se puede usar funciones de grupo en la clausula WHERE SELECT FROM WHERE GROUP BY department_id, AVG(salary) employees AVG(salary) > 8000 department_id; WHERE AVG(salary) > 8000 * ERROR at line 3: ORA-00934: group function is not allowed here 4-20 Excluyendo Resultados de Grupo EMPLOYEES El salario máximo por departamento cuando sea mayor a $10,000 … 4-21 Excluyendo Resultados de Grupo : La cláusula HAVING Usar la cláusula HAVING para restringir grupos: 1. Los registros son agrupados. 2. La función de grupo es aplicada. 3. Los grupos que coincidan con la clausula HAVING son desplegados por la consulta. SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY column, group_function table condition] group_by_expression] group_condition] column]; 4-22 Usando la cláusula HAVING SELECT FROM GROUP BY HAVING department_id, MAX(salary) employees department_id MAX(salary)>10000 ; 4-23 Usando la cláusula HAVING SELECT FROM WHERE GROUP BY HAVING ORDER BY job_id, SUM(salary) PAYROLL employees job_id NOT LIKE '%REP%' job_id SUM(salary) > 13000 SUM(salary); 4-24 Funciones de grupo anidadas Desplegar el máximo salario promedio. SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id; 4-25 Resumen En esta lección se habrá aprendido acerca de: • • • Uso de funciones de grupo COUNT, MAX, MIN, AVG Escribir búsquedas que usen la cláusula GROUP BY Escribir búsquedas que usen la cláusula HAVING SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY column, group_function(column) table condition] group_by_expression] group_condition] column]; 4-26 Practica 4 Visión General Esta practica cubre los siguientes temas: • • Escribir búsquedas que usen funciones de grupo • Excluir grupos usando la cláusula HAVING Agrupar registros para archivar mas de un resultado 4-27 4-28 4-29 4-30
© Copyright 2024