Inicio‎ > ‎SQL‎ > ‎Lecciones SQL‎ > ‎

T05 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 se corresponden con 1 hora de clase semanal, 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;

descripcionhoras
DISEÑO Y GESTION DE BASES DE DATOS4.00000
FUNDAMENTOS DE LAS BASES DE DATOS4.00000
FUNDAMENTOS DE LA PROGRAMACION6.00000
HISTORIA DE LA INFORMATICA3.00000
PROGRAMACION CONCURRENTE4.00000

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

descripcioncreditos
HISTORIA DE LA INFORMATICA4.5


Funciones


La lista completa de funciones disponibles en MySQL es mejor consultarla en su manual (https://dev.mysql.com/doc/refman/5.0/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;

redondeo
15.13

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 selectround(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. 

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
MINexpr )el más pequeño de todos los valores en expr
MAXexpr )el mayor de todos los valores en expr










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.


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

select count(*) profes from profesores;

profes
3


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

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

count(*)
5

¿Cuantos valores de créditos distintos hay?

select count(distinct creditos) quecreditos 
from asignaturas;

quecreditos
3

Hay 3 valores distintos de créditos: 6.0, 9.0 y 4.5.

codigodescripcioncreditoscreditosp
DGBDDISEÑO Y GESTION DE BASES DE DATOS6.03.0
FBDFUNDAMENTOS DE LAS BASES DE DATOS6.01.5
FPFUNDAMENTOS DE LA PROGRAMACION9.04.5
HIHISTORIA DE LA INFORMATICA4.5 
PCPROGRAMACION CONCURRENTE6.01.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 creditos.

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

filasvaloresdistintos
543

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

sinDisconDis
2.625003.00000

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

credsImp
18.0

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

credsImp
12.0
6.0

En realidad, 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

dni credsImp
21111222 12.0
21333444 6.0


El criterio definido en el group by (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

SQL Error: 'ejemplo.p.nombre' isn't in GROUP BY

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.


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

nombre credsPorProf
EVA GOMEZ 12.0
RAFAEL ROMERO 6.0

Aunque con el "nombre" bastaba, hemos puesto también el "DNI" por prevención, podría darse el caso de tener 2 profesores distintos que se llamaran igual.

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 puede utilizar para eliminar ciertas filas del cálculo.

Así, si queremos calcular cuantas asignaturas imparte cada profesor sin contar FBD, escribiremos la siguiente sentencia.:

select nombre, count(*)
from profesores p, imparte i
where p.dni = i.dni
    and asignatura != 'FBD'
group by p.dni, nombre;

nombre count(*)
EVA GOMEZ 1
RAFAEL ROMERO 1

 
Para entendernos, "primero" se ejecuta el 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 D.N.I., asumimos que no habrá duplicados en este atributo. No obstante, el nombre no será clave y, por lo tanto, admite duplicados. Es decir, es perfectamente posible encontrar a dos personas distintas que se llamen igual.

Nombre del coordinador y cuántas asignaturas coordina.

select nombre, count(*) coordina
from asignaturas a, coordinadores c
where c.asig = a.codigo
group by nombre;

ombre coordina
AGAPITO CIFUENTES 2
ROMUALDO GOMEZ 1

DNI y nombre del coordinador y cuántas asignaturas coordina.

select dni, nombre, count(*) coordina
from asignaturas a, coordinadores c
where c.asig = a.codigo
group by dni, nombre;

dni nombre coordina
10111222 AGAPITO CIFUENTES 1
55777666 AGAPITO CIFUENTES 1
66555444 ROMUALDO GOMEZ 1

Parece claro que la primera solución no es correcta, más si cabe teniendo en cuenta el diseño de la tabla y el significado de sus datos (un coordinador no puede coordinar más de una asignatura).



Ordenación de la salida

Se puede ordenar la salida utilizando las columnas calculadas.

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;



nombre asignaturas
RAFAEL ROMERO 1
EVA GOMEZ 2