Agregación
Group by
Recordemos que el uso de funciones de agregados nos permite realizar cálculos sobre la totalidad de filas que cumplen una determinada condición (o sobre todas las filas de la tabla) .
Cantidad de créditos que se imparten
select sum(creditos) credsImp
from imparte i, asignaturas a
where i.asignatura = a.codigo
Sin embargo, es muy habitual la necesidad de realizar ese cálculo no de forma global sino particularizando por algún criterio.
Cantidad de créditos que imparte cada profesor.
select sum(creditos) credsImp
from imparte i, asignaturas a
where i.asignatura = a.codigo
group by dni
Se entenderá mejor si introducimos más información en la salida
select dni, sum(creditos) credsImp
from imparte i, asignaturas a
where i.asignatura = a.codigo
group by dni
El criterio definido en el group by ("agrupar por dni"), establece los distintos grupos de filas sobre los que se va a realizar la suma. Digamos que el sistema busca cada uno de los valores distintos de dni dentro de la tabla imparte y que, para todas las filas que comparten cada uno de esos valores hace una suma de créditos.
A partir de aquí ya podemos añadir cualquier información que consideremos adecuada.
Nombre de los profesores y créditos que imparten.
select nombre, sum(creditos) credsPorProf
from imparte i, asignaturas a, profesores p
where i.asignatura = a.codigo
and p.dni = i.dni
group by p.dni;
El error en la interpretación de la orden anterior viene dado por que es obligatorio poner en el group by todas las columnas no calculadas que vayan a salir en el resultado.
SQL Error: 'ejemplo.p.nombre' isn't in GROUP BY
Al contrario, no es necesario que todas las columnas especificadas en el group by aparezcan en la salida.
select nombre, sum(creditos) credsPorProf
from imparte i, asignaturas a, profesores p
where i.asignatura = a.codigo
and p.dni = i.dni
group by p.dni, nombre;
Hemos agrupado también por "DNI" por prevención. Aunque en un estado de la base de datos los resultados sean el mismo —en este, todos los profesores tienen nombre distinto—, en otro estado podría ocurrir que dos profesores, dos DNI diferentes, compartieran nombre.
Nótese que partimos de un consulta con varias tablas y que el resultado mostrará, previsiblemente, al mismo profesor en varias filas. Sin embargo, en una consulta que utilizara únicamente una tabla, el uso del group by podría ser innecesario.
select codigo, sum(creditos) from asignaturas group by codigo
Obtendría el mismo resultado que
select codigo, creditos from asignaturas
Estamos pretendiendo agrupar por código de asignatura, la clave primaria de la única tabla que estamos consultando. Por lo tanto, no hay nada que agrupar, cada fila representa a una asignatura diferente, todas las sumas son de un único valor, ni tan siquiera las podemos llamar "sumas". No quiere esto decir que no se pueda agregar información en una única tabla, simplemente que debería ser otro el criterio de agrupación elegido.
Where y group by
Cuando se utiliza la cláusula where, aparte de enlazar tablas por columnas comunes, como PROFESORES e IMPARTE por profesores.dni e imparte.dni respectivamente, se eliminan ciertas filas del cálculo.
Así, si queremos calcular cuantas asignaturas imparte cada profesor sin contar FBD, escribiremos la siguiente sentencia.
Exceptuando 'FBD', calcula cuántas asignaturas imparte cada profesor.
select nombre, count(*)
from profesores p, imparte i
where p.dni = i.dni
and asignatura != 'FBD'
group by p.dni, nombre;
Para entendernos, "primero" se ejecuta from-where sin cálculos y "después", sobre las filas resultantes, se realizan los cálculos según el criterio de agrupación del group by. Con más detalle, podemos asumir este orden de ejecución:
from
where
group by
select
Atributos no clave
Hemos estado introduciendo la clave primaria de PROFESORES en los group by para prevenir cálculos, en principio, erróneos. Los únicos atributos que aseguran la identificación de una tupla respecto de las demás son los que forman la clave primaria (en general, clave candidata).
Así, si la clave primaria de una tabla de personas (profesores o alumnos, por ejemplo) es el DNI, asumimos que no habrá duplicados en este atributo. No obstante, el nombre no será clave y sí admite duplicados. Es decir, es perfectamente posible encontrar a dos personas distintas que se llamen igual.
Supongamos que la base de datos EJEMPLO contiene una tabla SUPERVISORES y otra SUPERVISA:
CREATE TABLE supervisores (
dni VARCHAR(10),
nombre VARCHAR(40),
PRIMARY KEY (dni)
);
CREATE TABLE supervisa (
dni VARCHAR(10),
asig CHAR(5),
PRIMARY KEY (dni,asig),
FOREIGN KEY (asig) REFERENCES asignaturas (codigo)
);
Nombre del supervisor y descripción de la asignatura que supervisa.
select s.nombre supervisor,a.descripcion asignatura
from supervisores s, supervisa r, asignaturas a
where s.dni=r.dni and r.asig=a.codigo;
Pudiera parecer que Agapito Cifuentes supervisa 3 asignaturas, pero
DNI y nombre del supervisor y descripción de la asignatura que supervisa.
select s.dni,s.nombre supervisor,a.descripcion asignatura
from supervisores s, supervisa r, asignaturas a
where s.dni=r.dni and r.asig=a.codigo;
Lo cierto es que hay dos personas diferentes que da la casualidad de que tienen el mismo nombre y apellido. Una supervisa dos asignaturas y la otra solo una.
Obviamente, esto afecta a la agregación.
select s.nombre supervisor,count(*) cuantas
from supervisores s, supervisa r, asignaturas a
where s.dni=r.dni and r.asig=a.codigo
group by s.nombre;
Lo correcto, en este caso, sería añadir el identificador al criterio de agrupación.
select s.dni,s.nombre supervisor,count(*) cuantas
from supervisores s, supervisa r, asignaturas a
where s.dni=r.dni and r.asig=a.codigo
group by s.dni,s.nombre;
Ordenación de la salida
Se puede ordenar la salida utilizando las columnas calculadas, incluso por otras funciones de agregados no presentes en el select.
Todas estas órdenes obtienen la misma salida:
Utilizando la propia expresión
select nombre, count(*) asignaturas
from profesores p, imparte i
where p.dni=i.dni
group by p.dni, nombre
order by count( * );
Utilizando la etiqueta de columna
select nombre, count(*) asignaturas
from profesores p, imparte i
where p.dni=i.dni
group by p.dni, nombre
order by asignaturas;
Utilizando el orden de la columna
select nombre, count(*) asignaturas
from profesores p, imparte i
where p.dni=i.dni
group by p.dni, nombre
order by 2;
