Agregación

Se pueden utilizar expresiones aritméticas tanto en la cláusula select, para obtener una nueva columna en la tabla resultado, como en la construcción de condiciones de selección de filas.

Suponiendo que el curso se divide en 2 semestres y que 3 créditos equivale a 1 hora de clase semanal durante el año completo, nombre de las asignaturas y número de horas de clase semanales de cada una en un único semestre.

select descripcion, (creditos/3)*2 horas
from asignaturas;

Los créditos de cada asignatura son anuales: divididos entre 3, obtenemos las horas a impartir durante un año lectivo cada semana; si lo reducimos a un único semestre, cada semana tendremos el doble de horas de clase.

Descripción de las asignaturas y número de horas semanales de las asignaturas con menos de 4 horas semanales de clase

select descripcion, creditos 
from asignaturas 
where (creditos/3)*2 < 4

Funciones

La lista completa de funciones disponibles en MySQL es mejor consultarla en su manual (https://dev.mysql.com/doc/refman/5.7/en/functions.html). Téngase en cuenta que cada SGBD puede proporcionar sus funciones que coincidirán o no con las que maneja MySQL.

Redondea 15.1297 a dos decimales

select round(15.1297,2) redondeo from dual;

Obviamente, para realizar y mostrar un cálculo como este no hace falta ninguna tabla. Dual es una tabla virtual que se utiliza en estos casos para mostrar datos no almacenados ni calculados a partir de ninguna tabla. En realidad, se mantiene por compatibilidad con otros SGBD (Oracle) ya que también se puede ejecutar select round(15.1297,2) con el mismo resultado.

Funciones de agregados

Se dispone de una serie de funciones de agregados que retornan valores calculados sobre una determinada columna o columnas.

La diferencia con las mostradas en la sección anterior es que estas funciones trabajan sobre conjuntos de valores, devuelven un único valor resultado de procesar varias tuplas seleccionadas mediante la condición de la cláusula where; si no se especifica ésta, el cálculo se realiza sobre la totalidad de la columna.

Las funciones de tipo estadístico precisan que la expresión que se evalúe se construya sobre columnas numéricas. La expresión expr puede contener el nombre de una columna o un cálculo sobre una o varias columnas.

Si se especifica la palabra clave distinct la expresión obligatoriamente ha de ser un nombre de columna, y se asume que la función se calcula únicamente sobre valores distintos de la expresión.

  • COUNT( * ) número de filas
  • COUNT( [DISTINCT] expr ) número de valores distintos en expr
  • SUM( [DISTINCT] expr ) suma de todos los valores en expr
  • AVG( [DISTINCT] expr ) promedio de todos los valores en expr
  • MIN( expr ) el más pequeño de todos los valores en expr
  • MAX( expr ) el mayor de todos los valores en expr

¿Cuántos profesores hay en nuestra base de datos?

select count(*) profes from profesores;

¿Cuántas asignaturas de más de 4 créditos tenemos?

select count(*) from asignaturas
where creditos > 4;

¿Cuantos valores de créditos distintos hay?

select count(distinct creditos) quecreditos 
from asignaturas;

Puedes comprobar que, efectivamente, hay 3 valores distintos de créditos en la tabla asignaturas: 6.0, 9.0 y 4.5.

Sobre el modificador DISTINCT

Todas las funciones de agregados (menos count(*)) ignoran los nulos (NULL). Por tanto, select count(creditos) from asignaturas devolvería la cantidad de filas en la tabla asignaturas que no tienen un nulo en la columna "creditos".

select count(*) filas, count(creditosp) valores, count(distinct creditosp) distintos from asignaturas;

El uso del modificador distinct implica, además, que no se tienen en cuenta los valores duplicados. Supongamos una tabla con cinco filas y una columna "colx" y que queremos obtener la media de los valores almacenados en ella, que son (NULL,1,1,1,3).

AVG(colx) nos devolvería 1.5, mientras que AVG(distinct colx) nos devolvería 2.

Por ejemplo:

select avg(creditosp) sinDis, avg(distinct creditosp) conDis from asignaturas;

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;