CONSULTAS CON AGRUPACION EN TABLAS SIMPLES Y TABLAS COMBINADAS

1.2. Estructura información, mediante consultas de actualización, agrupación y combinación de datos en el sistema gestor de bases de datos para su administración. 

15 horas 20%     12 Sep al 20Sep    25/09/2017

Hacer un clic aqui para accesar a la rubrica de evalaucion 1.2.1 y al cuadro de evaluación






Agrupaciones La cláusula GROUP BY

Hasta ahora las consultas de resumen que hemos visto utilizan todas las filas de la tabla y producen una única fila resultado.

1.  Una consulta con una cláusula GROUP BY se denomina consulta agrupada ya que agrupa los datos de la tabla origen y produce una única fila resumen por cada grupo formado. Las columnas indicadas en el GROUP BY se llaman columnas de agrupación.

Ejemplo

SELECT SUM(ventas) FROM repventas GROUP BY oficina

Se forma un grupo para cada oficina, con las filas de la oficina, y la suma se calcula sobre las filas de cada grupo.

El ejemplo anterior obtiene una lista con la suma de las ventas de los empleados de cada oficina. Se pueden obtener subtotales con la cláusula GROUP BY.

2.     Un columna de agrupación no puede ser de tipo memo u OLE.

3.      La columna de agrupación se puede indicar mediante un nombre de columna o cualquier expresión válida basada en una columna pero no se pueden utilizar los alias de campo

SELECT importe/cant , SUM(importe)

FROM pedidos

GROUP BY importe/cant

4.     Se pueden agrupar las filas por varias columnas, en este caso se indican las columnas separadas por una coma y en el orden de mayor a menor agrupación. Se permite incluir en la lista de agrupación hasta 10 columnas.

SELECT SUM(ventas)

FROM oficinas

GROUP BY region,ciudad

La cláusula HAVING

La cláusula HAVING nos permite seleccionar filas de la tabla resultante de una consulta de resumen.

 Para la condición de selección se pueden utilizar los mismos EJEMPLOS de comparación descritos en la cláusula WHERE, también se pueden escribir condiciones compuestas (unidas por los operadores OR, AND, NOT), pero existe una restricción.

 En la condición de selección sólo pueden aparecer:

1.     valores constantes

2.     funciones de columna

3.     columnas de agrupación (columnas que aparecen en la cláusula GROUP BY)

4.     cualquier expresión basada en las anteriores.

Ejemplo: Queremos saber las oficinas con un promedio de ventas de sus empleados mayor que 500.000 ptas.

SELECT oficina

FROM empleados

GROUP BY oficina

HAVING AVG(ventas) > 500000

NOTA: Para obtener lo que se pide hay que calcular el promedio de ventas de los empleados de cada oficina, por lo que hay que utilizar la tabla empleados.Tenemos que agrupar los empleados por oficina y calcular el promedio para cada oficina, por último nos queda seleccionar del resultado las filas que tengan un promedio superior a 500.000 ptas.

Funciones de columna

En la lista de selección de una consulta de resumen aparecen funciones de columna también denominadas funciones de dominio agregadas. Una función de columna se aplica a una columna y obtiene un valor que resume el contenido de la columna.

La función SUM()

Calcula la suma de los valores indicados en el argumento. Los datos que se suman deben ser de tipo numérico (entero, decimal, coma flotante o monetario...). El resultado será del mismo tipo aunque puede tener una precisión mayor.

SELECT SUM(SALARIO) FROM EMPLEADOS  Suma los salarios de toda la tabla

SELECT salario, count(*), sum(salario) FROM EMPLEADOS group by salario having salario = 2000

La función AVG()

Calcula el promedio (la media aritmética) de los valores indicados en el argumento, también se aplica a datos numéricos, y en este caso el tipo de dato del resultado puede cambiar según las necesidades del sistema para representar el valor del resultado.

Actividad de Aprendizaje

Ejercicio 6

Elabora las siguientes consultas en la base de datos escuela, y realiza el reporte del ejercicio No 6.

CONSULTA 1

REALIZAR UNA CONSULTA QUE AGRUPE LAS LOCLAIDADES DONDE VIVEN LOS MAESTROS Y MENCIONE LA CANTIDAD DE MAESTROS QUE VIVEN EN DICHA LOCALIDAD

SELECT COUNT(*) AS CANTIDAD, LOCALIDAD FROM MAESTROS GROUP BY LOCALIDAD

EJRCICIO7_CONSULTA1
CANTIDAD	LOCALIDAD
2	Cd.Guzman
1	tamazula

CONSULTA 2

Realizar una consulta que agrupe a los alumnos por grupo y mencione la cantidad que hay en cada grupo

SELECT COUNT (*) AS CANTIDAD,GRUPO FROM ALUMNOS  GROUP BY GRUPO

EJERCICIO7_CONSULTA2
CANTIDAD	GRUPO
2	503
3	505

CONSULTA 3

REALIZAR UNA CONSULTA DE AGRUPACION PARA MOSTRAR LA SUMA DE SALARIOS DE LOS MAESTROS QUE VIVEN UNICAMNETE EN CIUDAD GUZMAN

SELECT LOCALIDAD,  SUM(SALARIO) AS TOTALAPAGAR FROM MAESTROS GROUP BY LOCALIDAD HAVING LOCALIDAD = “CD.GUZMAN”

CONSULTA #4 Realizar una consulta de agrupación para mostrar la suma de faltas de los alumnos del grupo 505.

SELECT SUM(FALTAS)AS TOTALFALTAS FROM ALUMNOS GROUP BY GRUPO  HAVING GRUPO =505

EJERCICIO7_CONSULTA4
TOTALFALTAS
10

 5.  Muestra el promedio de faltas que tienen los alumnos de cada grupo sin importar cual sera este.

SELECT Grupo, AVG (Faltas) AS Promediofaltas FROM Alumnos GROUP BY Grupo

Ejercicio7_Consulta5
Grupo	Promediofaltas
503	2
505	3.33333333333333

6.-  Muestra el promedio de salario que tienen los maestros que viven en Guzmán

SELECT localidad, AVG (salario) AS PromedioSalario FROM Maestros GROUP BY  localidad  HAVING LOCALIDAD ="Cd.Guzman"

ejercicio7_consulta6
localidad	PromedioSalario
Cd.Guzman	$6,500.00

ACTIVIDAD DE EVALUACIÓN PRACTICA 5

Ahora elabora en tu libreta 6 enunciados de consulta para resolver en la base de datos personal que se están manejando para cada actividad de evaluación.

AGRUPAMIENTO EN COMBINACION DE TABLAS

Si los datos que necesitamos utilizar para obtener nuestro resumen se encuentran en varias Tabla resumen con multi tablas, formamos el origen de datos adecuado en la cláusula FROM como si fuera una consulta multitabla normal.

El ejemplo mas claro es cuando se desean extraer datos de 2 o mas tablas y ala vez agruparlas.

CONSULTA 1

MOSTRAR LA CANTIDAD DE ALUMNOS A LOS QUE ATIENDE CADA MAESTRO EN CADA GRUPO

SELECT MAESTROS.APELLIDO, ALUMNOS.GRUPO, COUNT(*) AS TOTALALUMNOS FROM ALUMNOS LEFT JOIN MAESTROS ON ALUMNOS.NO_TRABAJADOR = MAESTROS.NOTRABAJADOR GROUP BY MAESTROS.APELLIDO, ALUMNOS.GRUPO

EJERCICIO8_CONSULTA1
APELLIDO	GRUPO	TOTALALUMNOS
celis Crisostomo	503	1
muños collaso	505	1
vivas torres	503	1
vivas torres	505	2

Se estan mostrando datos de las dos tablas como el apellido de maestro y el grupo donde se encuentra el alumno asignado y esta contabilizando cuantos alumnos tiene cada maestro en cada grupo.

ACTIVIDAD DE EVALUACIÓN PRACTICA 5

Ahora elabora en tu libreta 6 enunciados de consulta para resolver en la base de datos personal que se están manejando para cada actividad de evaluación.