1. Consultas agrupadas

En ocasiones es necesario realizar operaciones sobre los campos calculados. Es en este momento cuando las consultas que hemos visto hasta ahora se quedan cortas y tenemos que recurrir a un determinado tipo que opera sobre los resultados.

Base permite la creación de consultas que incluyen funciones, es decir, consultas que realizan operaciones sobre los resultados para calcular, a su vez, otros campos. Ejemplos de éstas son: sumar todos los valores de un grupo de registros, hallar el valor medio, contar el número total de registros o averiguar el valor máximo y mínimo de un conjunto.

El uso de estas funciones viene determinado por las consultas denominadas de agrupación. En estas consultas los registros se clasifican según determinados criterios y a partir de estas clasificaciones se aplican las funciones disponibles. Entre ellas encontramos:

1.1. Consulta 1

Vamos a crear una consulta que muestre el nombre de cada género almacenado y el total de películas que tenemos de cada género. Es decir, realizar una consulta sobre la tabla PELICULA de manera que agrupemos las filas devueltas en función de cada género para así poder contarlas y saber el número de películas asociadas a cada uno de ellos.

• Abre la base de datos "videoclub".
• Crea una consulta en vista diseño.
• Añade la tabla PELICULA.
• Selecciona los campos "Genero" y "Titulo". Los campos que necesitamos son, por un lado el “Genero”, que es sobre el que agruparemos los resultados devueltos y, por otro, un campo de la tabla PELICULA que estemos seguros que siempre tendrá un valor (no estará vacío) para cada fila de películas. Por ejemplo "Titulo" o "Id_pelicula".
• Ve a la columna "Genero" y establece un orden ascendente.
• En el campo Función selecciona la opción Agrupar.

En segundo lugar, lo que queremos es contar las películas relacionadas con cada género.

• Ve a la columna "Titulo". En el apartado Alias escribe "Total de películas". En el apartado Función elige la opción Contar.
• El diseño quedará de la siguiente forma:

• Guarda la consulta como CG_TOTAL_GENERO.
• Ejecuta la consulta y comprueba que funciona correctamente. Para ello puedes contar manualmente las películas de cada género de la tabla PELICULA:

Como se puede observar, para realizar una operación sobre los campos necesitaremos, al menos, un campo sobre el que realizar la agrupación y otro campo sobre el que realizar la operación. En el ejemplo anterior el campo sobre el que se agrupa sería "Genero", mientras el campo sobre el que se realiza la operación sería "Titulo", aunque como hemos dicho, podría ser también el campo "Id_pelicula". De hecho, si realizamos la consulta cambiando el campo "Titulo" por "Id_pelicula":

El resultado será el mismo:

1.2. Consulta 2

Ahora crearemos una consulta que calcule el promedio de duración de las películas agrupadas por "Soporte", es decir, para cada soporte se deberá obtener la media de las duraciones de las películas.

• Crea una consulta en vista diseño.
• Añade la tabla PELICULA.
• Selecciona los campos "Duracion" y "Soporte". Los campos que necesitamos son, por un lado el “Soporte”, que es sobre el que agruparemos los resultados devueltos y, por otro, un campo de la tabla PELICULA que contenga los valores para realizar la operación.
• Ve a la columna "Soporte" y establece un orden ascendente. En el campo Función selecciona la opción Agrupar.

• Ve a la columna "Duracion". En el apartado Alias escribe "Media de duraciones". En el apartado Función elige la opción Promedio.
• El diseño quedará de la siguiente forma:

• Guarda la consulta con nombre CG_MEDIA_DURACION.
• Ejecuta la consulta y comprueba que funciona correctamente. Para ello puedes realizar la media manualmente de las duraciones de la tabla PELICULA:

1.3. Consulta 3

También podemos realizar consultas para que calcule una determinada operación para todos los registros, es decir, sin que agrupe por campos. En este caso tenemos que seleccionar el campo sobre el que queremos aplicar la función y la función en sí.

Vamos a realizar una consulta en la que se calcule la suma de todas las duraciones.

• Crea una consulta en vista diseño.
• Añade la tabla PELICULA.
• Selecciona el campo "Duracion", ya que únicamente queremos aplicar la función sobre éste.
• Ve a la columna "Duracion". En el apartado Alias escribe "Suma de duraciones". En el apartado Función elige la opción Suma.
• El diseño quedará de la siguiente forma:

• Guarda la consulta con nombre CG_SUMA_DURACION.
• Ejecuta la consulta y comprueba que funciona correctamente. Para ello puedes realizar la suma manualmente de las duraciones de la tabla PELICULA:

Este funcionamiento sirve para todas las demás funciones y permite obtener, rápidamente, resultados que pueden servirnos para realizar otros cálculos.

1.4. Más consultas

Crea las siguientes consultas en vista diseño:

• Consulta con nombre CG_MAX_GENERO sobre la tabla PELICULA en la que se obtenga el máximo de las duraciones de cada Género.
• Consulta con nombre CG_TOTAL_SOPORTE sobre la tabla PELICULA en la que se obtenga el número de películas de cada soporte.
• Guarda los cambios.

2. Funciones complejas

Además de las operaciones básicas, habrás observado que el desplegable donde se selecciona la función posee más opciones que no hemos visto. Estas opciones tienen una mayor complejidad y vamos a estudiarlas en este punto.

2.1. Función Todo

La función Todo se utiliza en el caso de campos de tipo Boolean, es decir, cuyo valor es Sí o No. Ésta permite saber si todos los valores son Sí o hay alguno que no lo es, de modo que si todos lo son obtendrá un Sí, mientras que en caso contrario obtendrá un No.

Veamos un ejemplo, aunque previamente vamos a modificar la tabla PELICULA para insertar un campo Booleano.

Tabla PELICULA. Añadir campo

• Abre la tabla PELICULA en modo Diseño (editar).
• Sitúate al final e inserta un campo llamado "Original", cuyo tipo será Boolean:

• Cierra la tabla.

Tabla PELICULA. Edición de datos

• Abre la tabla en modo edición de datos.
• Cambia los valores para que la columna "Original" quede de la siguiente forma:

• Guarda los cambios en la tabla.
• Cierra la tabla.

Crear consulta

Ahora sí que podemos realizar la consulta con la función Todo.

• Crea una consulta en vista diseño.
• Añade la tabla PELICULA.
• Selecciona el campo "Original".
• Ve a la columna "Original" y en el apartado Alias escribe "¿Todas originales?". En el apartado Función elige la opción Todo.

• Guarda la consulta con nombre CGC_TODO_ORIGINAL.
• Ejecuta la consulta y comprueba que funciona correctamente.

Como podemos observar, al haber varias de ellas que no son originales, el resultado será negativo. Si modificamos los valores para que todas sean originales y volvemos a realizar la consulta:

El resultado será:

2.2. Función Alguno

La función Alguno es similar a la anterior, pero en este caso la función devolverá Sí en caso de que alguno de los valores sea Sí y No en caso de que todos los valores sean No.

Crear consulta

• Crea una consulta en vista diseño.
• Añade la tabla PELICULA.
• Selecciona el campo "Original".
• Ve a la columna "Original" y en el apartado Alias escribe "¿Alguna original?". En el apartado Función elige la opción Alguno.

• Guarda la consulta con nombre CGC_ALGUNO_ORIGINAL.
• Ejecuta la consulta y comprueba que funciona correctamente.

Obviamente, como hemos cambiado los valores para que todas sean originales indicará que, al menos una, es original. Cambiemos los valores como se indica en la figura:

El resultado será:

2.3. Función STDDEV_SAMP

La función STDDEV_SAMP es una función estadística que devuelve la desviación típica de un grupo de números, es decir, devuelve la cantidad que se han desviado con respecto de la media.

Vamos a crear una consulta para saber la desviación típica de la duración de las películas.

• Crea una consulta en vista diseño.
• Añade la tabla PELICULA.
• Selecciona el campo "Duracion".
• Ve a la columna "Duracion" y en el apartado Alias escribe "Desviación de la duración". En el apartado Función elige la opción STDDEV_SAMP.

• Guarda la consulta con nombre CGC_DESV_DURACION.
• Ejecuta la consulta y comprueba que funciona correctamente.

2.4. Función VAR_SAMP

La función VAR_SAMP es una función que devuelve la varianza de un grupo de números, es decir, la medida de dispersión que equivale al cuadrado de la desviación típica. Los pasos para calcularla son similares a la anterior.

Vamos a crear una consulta para saber la varianza de la duración de las películas.

• Crea una consulta en vista diseño.
• Añade la tabla PELICULA.
• Selecciona el campo "Duracion".
• Ve a la columna "Duracion" y en el apartado Alias escribe "Varianza de la duración". En el apartado Función elige la opción VAR_SAMP.

• Guarda la consulta con nombre CGC_VAR_DURACION.
• Ejecuta la consulta y comprueba que funciona correctamente.

Cerrar la base de datos

• Guarda los cambios en la base de datos.
• Cierra la base de datos "videoclub".