6 - Funciones de Agregado

Dejemos de lado por ahora los tipos de dato y sus operaciones para abordar un recurso del SQL de gran potencia que resulta muy interesante: Totalizar datos.

Tal como está planteado este curso, no podría exponerse este concepto de otro modo que no fuese mediante ejemplos prácticos. Supongamos entonces que nos piden lo siguiente:

¿Cuál es el salario medio de los empleados?

Bien, si recuperamos las preguntas de construcción que tanto nos ayudan para construir nuestras consultas SQL:

• ¿Qué datos nos piden?
• ¿Dónde están los datos?
• ¿Qué requisitos deben cumplir los registros?

A la pregunta: ¿Dónde están los datos?, se nos plantea una duda. El dato que nos piden es: “el salario medio de los empleados”, pero este dato no está en ninguna tabla, entonces ¿Cómo resolvemos el problema? Afortunadamente el SQL nos permite calcularlo, para calcular el salario medio basta con sumar todos los salarios y dividirlo por el número de empleados. Esto es posible hacerlo mediante las funciones de agregado SUM (suma) y COUNT (contar) de SQL.

Funciones SUM y COUNT

Obtengamos primero la suma de salarios, para ello nos hacemos las preguntas de construcción:

• ¿Qué datos nos piden?
  La suma de los salarios.
• ¿Dónde están los datos?
  En la tabla empleados.
• ¿Qué requisitos deben cumplir los registros?
  Ninguno, queremos sumar todos los salarios por lo tanto no hemos de filtrar registros, los seleccionaremos todos, o lo que es lo mismo, omitiremos la cláusula WHERE de la consulta SQL.

La consulta la construiríamos así:

select sum(SALARIO)
  from EMPLEADOS
            

Resultado:

Fíjese que el resultado de esta consulta SQL devuelve una sola fila. Obsérvese también que el motor SQL debe recorrer toda la tabla para obtener el resultado, puesto que debe sumar todos los salarios. De hecho lo que hace el motor SQL es lo que da nombre a las funciones de agregado, ya que recorre los registros seleccionados y va agregando al subtotal el valor que contiene cada registro.

Análogamente contamos el número de empleados, es decir, el número de registros de la tabla EMPLEADOS.

select count(*)
  from EMPLEADOS
            

Resultado:

El asterisco que encontramos en COUNT(*) equivale a decir: la fila completa. Fíjese que en este caso queremos contar registros, por lo tanto, el motor no mira el contenido de las columnas, sino que simplemente cuenta cuántas filas hay. Veremos en otra lección las particularidades de la función COUNT aplicada a un solo campo; por ahora, entendamos que COUNT(*) cuenta los registros seleccionados.

Ahora ya podemos resolver la cuestión planteada, basta con dividir el primer resultado por el segundo, pero... vayamos más allá. De entrada estamos recorriendo la tabla dos veces, una para sumar los salarios, y otra para contar los empleados, es de esperar que recorriéndola una sola vez el motor SQL sea capaz de reunir ambos datos. Y así es, para ello construimos la siguiente consulta SQL:

select sum(SALARIO), count(*)
  from EMPLEADOS
            

Resultado:

Vayamos todavía un poco más allá. En la lección anterior se mencionó, a modo de apunte, que el SQL permite calcular algunas operaciones matemáticas en la cláusula SELECT. No parece descabellado esperar entonces, que pueda dividir estas dos columnas obteniendo, en única columna, el resultado de la división:

select sum(SALARIO) / count(*)
  from EMPLEADOS
            

Resultado:

Efectivamente funciona. Con esto queda resuelta la cuestión planteada.

Alias de Campo

Este es un buen momento para hacer un paréntesis y explicar los Alias de campo de SQL.

Usted, como informático que además de construir la anterior consulta conoce bien el lenguaje SQL, no tendrá dificultades en interpretar la cabecera de la columna del resultado anterior: “SUM(SALARIO) / COUNT(*)”. Pero... estaremos de acuerdo en que no podría entregar a su jefe un informe con semejante encabezamiento. Para solucionar esto el SQL pone a su disposición la palabra clave AS, que permite rebautizar con un alias o sobrenombre las cabeceras de las columnas de resultado:

select sum(SALARIO) / count(*) as MEDIA_SALARIOS
  from EMPLEADOS
            

Resultado:

Si desea poner como alias una frase, o varias palabras separadas por espacios, deberá poner comillas dobles para indicarle al SGBD donde empieza y acaba el alias.

select sum(SALARIO) / count(*) as "MEDIA SALARIOS"
  from EMPLEADOS
            

Resultado:

Con ello usted ha conseguido un título mucho más explícito, además de ahorrarse una bronca de su jefe.

En general podemos rebautizar cualquier campo o expresión de la cláusula SELECT, para ello basta con colocar seguido del campo que interese la palabra clave AS, precediendo al ALIAS que se quiere aplicar. Pongamos otro ejemplo: nos piden una consulta que devuelva el nombre, apellidos y sueldo de todos los empleados, pero con los encabezamientos de cada columna en inglés:

select NOMBRE as NAME, APELLIDOS as SURNAMES, SALARIO as SALARY
  from EMPLEADOS
            

Resultado:

En realidad la palabra clave AS es solo un modo de enfatizar que se está renombrando el campo de tabla, puede omitirla y el resultado será el mismo. El motor SQL entiende que si después de un campo en la cláusula SELECT, colocamos una palabra y ambas están separadas por un espacio en lugar de una coma, la primera es un campo de tabla y la segunda es su alias o sobrenombre. Pruebe a llevar la anterior consulta a la Consola SQL eliminando antes de ejecutarla las palabras claves AS, dejando como mínimo un espacio entre los campos de tabla y sus alias.

Función AVG

Sigamos con la totalización de datos. El asunto de la media de salarios ha quedado resuelto, pero en realidad nos hemos complicado la vida sobremanera. Se ha hecho así adrede, con el propósito de ver más recursos del lenguaje y explicar mejor la totalización de datos.

En SQL existe un modo más simplificado para calcular la media de un campo mediante la función AVG (average, término en inglés que significa promedio). Es de hecho una función comoSUMo COUNT, pero con distinta funcionalidad. La consulta es equivalente a la que construimos anteriormente y quedaría de la siguiente manera:

select avg(SALARIO) as "MEDIA SALARIOS"
  from EMPLEADOS
            

Resultado:

Funciones de agregado con DISTINCT

Hasta ahora, las funciones de agregado han tenido en cuenta todos los registros seleccionados. Sin embargo, existe un recurso muy útil: podemos pedirle a la función que ignore los duplicados y realice el cálculo únicamente sobre los distintos valores que existan en una columna.

Para lograrlo, colocamos la palabra clave DISTINCT dentro de los paréntesis de la función, justo antes del nombre del campo.

Veamos la diferencia entre contar con o sin DISTINCT la columna SEXO de la tabla EMPLEADOS:

select count(SEXO) as TOTAL_REGISTROS,
       count(distinct SEXO) as DIFERENTES_VALORES
  from EMPLEADOS
            

Sin DISTINCT, el resultado es el número total de registros. Con DISTINCT, el motor SQL solo considera los diferentes valores al contar. Esto es extensible a las funciones SUM y AVG. Si un valor aparece repetido en la columna solo computará una vez.

Este recurso es muy práctico para, por ejemplo, saber cuántos productos diferentes se han vendido con count(distinct ID_ARTICULO) sobre una hipotética tabla de VENTAS.

Restricciones en Funciones de Agregado

Antes de finalizar debo insistir en que estas funciones no devuelven un dato de la tabla, sino que devuelven un cálculo en función de los datos que contienen los registros seleccionados, dando como resultado una única fila. Por lo que no tiene sentido mezclar en la cláusula SELECT campos de la tabla con funciones de agregado. Una consulta como la siguiente no tiene sentido, y el SGBD devolverá un error:

select NOMBRE, avg(SALARIO)
  from EMPLEADOS
            

Si estoy obteniendo un dato calculado sobre un grupo de registros, ¿qué sentido tiene acompañarlo de un dato singular de un solo registro? En la próxima lección abordaremos esto con más detalle pero, veamos como estas situación puede darse fácilmente al malinterpretar este recurso, por ejemplo, supongamos que a usted le piden: ¿qué porcentaje del dinero que desembolsa la empresa percibe cada empleado?

El porcentaje de un empleado = (salario_empleado / total_salarios) x 100.

Usted puede pensar incorrectamente en crear la siguiente consulta:

select SALARIO / sum(SALARIO) * 100 as PORCENTAJE
  from EMPLEADOS
            

Pero fíjese lo que usted pretende que el motor SQL haga. Primero debe obtener la suma de salarios, para ello debe recorrer toda la tabla, acto seguido, con el total de la suma resuelto, debe volver a recorrer la tabla para aplicar la fórmula a cada registro y obtener así los porcentajes de todos los empleados. Bien, esto no funciona así. Puede ayudarle a no caer en este error el saber que: con una consultas SQL donde sólo interviene una tabla, el motor SQL jamás recorrerá la tabla dos veces para brindarle el resultado. Resolvamos esta cuestión por partes.

Primero obtenemos el total de sueldos:

select sum(SALARIO)
  from EMPLEADOS
            

Y aplicado a cada empleado obtenemos los porcentajes:

select NOMBRE, APELLIDOS, SALARIO / 5525.5 * 100 as PORCENTAJE
  from EMPLEADOS
            

Resultado:

En la tercera parte del curso se trata cómo resolver esta cuestión realizando una sola consulta.

Funciones MAX y MIN

Para concluir veamos las funciones MAX (máximo) y MIN (mínimo), que intuitivamente ya se ve que se utilizan para obtener el valor máximo y mínimo de un campo de entre todos los registros seleccionados. Pero... no voy a poner ejemplos esta vez, le propongo a usted que intente resolver el ejercicio sobre las funciones MAX y MIN que encontrará al final de la lección, puede tomar como patrón las consultas de ejemplo vistas anteriormente donde aparecen las funciones SUM, COUNT o AVG, ya que las funciones MAX y MIN se aplican de igual modo a una consulta SQL.

Usted puede pensar que en este caso la funciones de agregado MAX yMIN sí devuelven un dato de la tabla, y es verdad, devolverán el valor máximo o mínimo del campo que indiquemos a cada una de ellas pero, en realidad no deja de ser un cálculo. El resultado no tiene por qué estar vinculado a un solo registro. El motor SQL calcula el valor máximo o mínimo sobre un grupo de registros y usted no puede saber de entrada si ese dato está en uno, dos, o más registros.

Resumen

Las funciones de agregado SUM (suma), COUNT (contar), AVG (promedio), MAX (máximo) y MIN (mínimo), devuelven en una sola fila el cálculo sobre un campo aplicado a un grupo de registros. Los registros que intervienen para el cálculo dependen de los filtros establecidos en la cláusula WHERE, interviniendo todos los registros de la tabla si la omitimos.

Podemos realizar varios cálculos sobre el mismo grupo de registros de una sola vez indicando varias funciones separadas por comas en la cláusula SELECT, pero no podemos mezclar en dicha cláusula campos de la tabla con funciones de agregado, puesto que carece de sentido.

Indicando DISTINCT antes del nombre de campo en la función de agregado, el motor solo considerará los diferentes valores de la columna para realizar el cálculo agregado.

El SQL nos permite rebautizar cualquier campo de la consulta por un alias o sobrenombre mediante la palabra clave AS:

select CAMPO1 as ALIAS1, CAMPO2 as ALIAS2, ....
            

select avg(SALARIO) as MEDIA_SALARIO_HOMBRES
  from EMPLEADOS
 where SEXO = 'H'
                    

select min(SALARIO) as SALARIO_MINIMO, max(SALARIO) as SALARIO_MAXIMO
  from EMPLEADOS
                    

select sum(SALARIO) as TOTAL_SALARIO_MUJERES
  from EMPLEADOS
 where SEXO = 'M'