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 select-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.

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)
) ENGINE=InnoDB;

CREATE TABLE supervisa (
 dni VARCHAR(10),
 asig CHAR(5),
 PRIMARY KEY (dni,asig),
 FOREIGN KEY (asig) REFERENCES asignaturas (codigo)
) ENGINE=InnoDB;

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;
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;