[8.1] funciones de cálculo con grupos

Ya hemos visto cómo hacer cálculos con los datos de una consulta e incluso como utilizar funciones en esos cálculos. Pero hasta ahora las funciones utilizadas solo podían usar información procedente de datos de la misma fila. Es decir, no podíamos sumar, por ejemplo, datos procedentes de distintas filas. Sin embargo, hacer cálculos con datos de diferentes filas es una necesidad muy habitual.

Las funciones de cálculo con grupos son las encargadas de realizar cálculos en vertical (usando datos de diferentes filas) en lugar de en horizontal (usando datos procedentes de la misma fila en la que vemos el resultado).

Las funciones de cálculos de totales para datos agrupados son las siguientes:

Todas ellas requieren trabajar con grupos (más adelante se explica cómo agrupar filas), si no se indican grupos, las funciones trabajan sobre todos los datos de una tabla.

Por ejemplo si tenemos esta tabla:

Suponiendo que son los datos de la tabla trabajadores, la consulta:

SELECT MAX(salario) FROM trabajadores;

Devuelve el valor 1650, que es el máximo salario. Mientras que la consulta:

SELECT COUNT(*) FROM trabajadores;

Devolvería 10, puesto que hay 10 trabajadores en la tabla.

[8.1.1]manejo de los valores nulos

Las funciones de cálculo agrupado ignoran los valores NULL. Si no deseamos ignorarles, deberemos utilizar funciones de nulos como COALESCE, NVL o NVL2 para manejar apropiadamente el valor nulo.

Sin embargo, la función COUNT, se suele usar con asterisco (*) como objeto de cálculo, lo que hace que cuente filas independientemente de su contenido. Si usamos una expresión, como COUNT(salario), no contaría las filas que tengan un salario nulo en ellas.

[8.1.2]uso de DISTINCT en las funciones de totales

Las funciones anteriores admiten que antepongamos el término DISTINCT antes de la expresión. De ese forma solo se tienen en cuenta los valores distintos. Por ejemplo en:

SELECT COUNT(DISTINCT salario)

El resultado es 4 porque solo hay 4 salarios distintos. Lo mismo ocurre con el resto de funciones, DISTINCT hace que se ignoren los valores repetidos.

[8.2] agrupaciones

Lo normal es utilizar las funciones anteriores, no para una tabla completa sino para grupos de filas en base a un criterio. Esta técnica es la que se conoce como agrupación de filas y provoca que, de cada grupo, se muestre una sola fila.

Para ello se utiliza la cláusula GROUP BY que permite indicar en base a qué registros se realiza la agrupación.

[8.2.1]sintaxis completa de la instrucción SELECT

Con GROUP BY la sintaxis completa instrucción SELECT queda de esta forma:

SELECT listaDeExpresiones

FROM listaDeTablas

[JOIN tablasRelacionadasYCondicionesDeRelación]

[WHERE condiciones]

[GROUP BY grupos]

[HAVING condicionesDeGrupo]

[ORDER BY listaDeExpresiones];

[8.2.2]uso de GROUP BY

En el apartado GROUP BY, se indica el nombre de las columnas (o expresiones más complejas) por las que se agrupa. La función de este apartado es crear una única fila por cada valor distinto en las columnas del grupo. Si por ejemplo agrupamos en base a las columnas tipo y modelo en una tabla de existencias, se creará un único registro por cada tipo y modelo distintos:

Si la tabla de existencias sin agrupar es:

Si ahora ejecutamos la siguiente instrucción:

SELECT tipo,modelo

FROM existencias

GROUP BY tipo,modelo;

Obtendremos este resultado:

Los datos se resumen. Por cada tipo y modelo distintos se creará un grupo de modo que solo aparece una fila por cada grupo. Los datos n_almacen y cantidad no están disponibles ya que varían en cada grupo. Solo se podrá mostrar los datos agrupados o datos sobre los que realicemos cálculos.

Es decir, esta consulta es errónea:

SELECT tipo,modelo, cantidad

FROM existencias

GROUP BY tipo,modelo;

SELECT tipo,modelo, cantidad

                    *

ERROR en línea 1: 
ORA-00979: no es una expresión GROUP BY 

La razón es el uso de la columna cantidad, en el SELECT como no se ha agrupado por ella y no es un cálculo sobre tipo y/o modelo (las columnas por la que ese está agrupando), se produce el error.

[8.2.3]uso de funciones de totales con grupos

Lo normal es agrupar para obtener cálculos sobre cada grupo. Por ejemplo podemos modificar la consulta anterior de esta forma:

SELECT tipo,modelo, cantidad, SUM(Cantidad)

FROM existencias

GROUP BY tipo,modelo;

y se obtiene este resultado:

Se suman las cantidades para cada grupo. Igualmente podríamos realizar cualquier otro tipo de cálculo (AVG, COUNT,…)

[8.3] condiciones HAVING

A veces se desea restringir el resultado de una expresión agrupada, por ejemplo podríamos tener esta idea:

SELECT tipo,modelo, cantidad, SUM(Cantidad)

FROM existencias

WHERE SUM(Cantidad)>500

GROUP BY tipo,modelo;

Lo que se espera es que solo aparezcan los grupos cuya suma de la cantidad sea menor de 500. Pero, en su lugar, Oracle devolvería este error:

WHERE SUM(Cantidad)>500

      *

ERROR en línea 3: 
ORA-00934: función de grupo no permitida aquí

La razón reside en el orden en el que se ejecutan las cláusulas de la instrucción SELECT. Oracle calcula primero el WHERE y luego los grupos (cláusula GROUP BY); por lo que no podemos usar en el WHERE la función de cálculo de grupos SUM, porque es imposible en ese momento conocer el resultado de la SUMA al no haberse establecido aún los grupos.

La solución es utilizar otra cláusula: HAVING, que se ejecuta una vez realizados los grupos. Es decir, si queremos ejecutar condiciones sobre las funciones de totales, se debe hacer en la cláusula HAVING.

La consulta anterior quedaría:

SELECT tipo,modelo, cantidad, SUM(Cantidad)

FROM existencias

GROUP BY tipo,modelo

HAVING SUM(Cantidad)>500;

Eso no implica que no se pueda usar WHERE. Ésta expresión sí es válida:

SELECT tipo,modelo, cantidad, SUM(Cantidad)

FROM existencias

WHERE tipo!=’AR’

GROUP BY tipo,modelo

HAVING SUM(Cantidad)>500;

En definitiva, el orden de ejecución de la consulta marca lo que se puede utilizar con WHERE y lo que se puede utilizar con HAVING:

[8.3.1]orden de ejecución de ínstrucción SELECT

Para evitar problemas conviene conocer la forma de trabajar de una instrucción SELECT. Estos son los pasos en la ejecución de una instrucción de agrupación por parte del gestor de bases de datos:

[1]Seleccionar las filas deseadas utilizando WHERE. Esta cláusula eliminará columnas en base a la condición indicada

[2]Se establecen los grupos indicados en la cláusula GROUP BY

[3]Se calculan los valores de las funciones de totales (COUNT, SUM, AVG,...)

[4]Se filtran los registros que cumplen la cláusula HAVING

[5]El resultado se ordena en base al apartado ORDER BY.

[8.4] funciones de agrupación anidadas

Es posible obtener resultados muy interesantes gracias a la posibilidad de anidar funciones de totales. Así podemos ejecutar esta instrucción:

SELECT AVG(SUM(Cantidad))

FROM existencias

GROUP BY tipo,modelo;

Para ello hay que tener en cuenta que la consulta funciona así:

[1]Se calcula la suma de la cantidad para cada grupo, el resultado es una cantidad por cada tipo y modelo distintos en la tabla

[2]A partir de esa resultado se calcula la media de las cantidades obtenidas en el punto anterior.

Por lo tanto el resultado es la media de la suma de cantidades de cada grupo.