PDF de programación - 1. DML. Las consultas de resumen

1. DML. Las consultas de resumen

1.1 Introducción


Una de las funcionalidades de la sentencia SELECT es el permitir obtener resúmenes de los datos

contenidos en las columnas de las tablas.

Para poder llevarlo a cabo la sentencia SELECT consta de una serie de cláusulas específicas (GROUP
BY, HAVING), y Transact-SQL tiene definidas unas funciones para poder realizar estos cálculos, las funciones
de agregado (también llamadas funciones de columna).

La diferencia entre una consulta de resumen y una consulta de las que hemos visto hasta ahora es que
en las consultas normales las filas del resultado se obtienen directamente de las filas del origen de datos y
cada datos que aparece en el resultado tiene su dato correspondiente en el origen de la consulta mientras que
las filas generadas por las consultas de resumen no representan datos del origen sino un total calculado sobre
estos datos. Esta diferencia hará que las consultas de resumen tengan algunas limitaciones que veremos a lo
largo del tema.

Un ejemplo sería:


SELECT Oficina, Region, Ventas

SELECT Region, SUM (Ventas)

FROM Oficinas

ORDER BY Region

FROM Oficinas

GROUP BY Region

Oficina

Ventas

150.000 Pts.

Region
centro
centro
este
este
este
este
norte
oeste
oeste

Suma de Ventas

0 Pts.
368.000 Pts.
735.000 Pts.
693.000 Pts.

24
23
28
13
12
11
26
22
21

A la izquierda tenemos una consulta simple que nos saca las oficinas con sus ventas ordenadas por
región, y a la derecha una consulta de resumen que obtiene la suma de las ventas de las oficinas de cada
región


Region
centro
este
norte
oeste

185.000 Pts.
836.000 Pts.

150.000
1.796.000

1.021.000



1.2 Las funciones de agregado


Una función de agregado SQL acepta un grupo de datos (normalmente una columna de datos) como
argumento, y produce un único dato que resume el grupo. Por ejemplo la función AVG() acepta una columna
de datos y devuelve la media aritmética (average) de los valores contenidos en la columna.

El mero hecho de utilizar una función de agregado en una consulta, convierte ésta en una consulta de

resumen.


Todas tienen una estructura muy parecida:


Función ([ALL|DISTINCT] expression)


El grupo de valores sobre el que actúa la función lo determina el resultado de la expresión que será un
nombre de columna o una expresión basada en una columna o varias del origen de datos. No se permiten
utilizar como argumentos funciones de agregado ni subconsultas. En la expresión nunca puede aparecer una
función de agregado ni una subconsulta.


1

La palabra ALL indica que se tiene que tomar en cuenta todos los valores de la columna. Es el valor por

defecto.

WHERE (si la hubiera).

La palabra DISTINCT hace que se consideren todas las repeticiones del mismo valor como uno sólo.
Todas las funciones de agregado se aplican a las filas del origen de datos una vez ejecutada la cláusula

Si exceptuamos la función COUNT, todas las funciones de agregado ignoran los valores NULL.
Una función de agregado puede aparecer en la lista de selección en cualquier lugar en el que puede
aparecer un nombre de columna. Puede, por ejemplo, formar parte de una expresión pero no se pueden anidar
funciones de agregado.

Tampoco se pueden mezclar funciones de columna con nombres de columna ordinarios. Hay

excepciones a esta regla pero cuando definimos agrupaciones y subconsultas que veremos más adelante.

1.2.1 La función COUNT



COUNT ({[ALL|DISTINCT] expresion | * } )


Expresion puede ser de cualquier tipo excepto text, image o ntext. No se permite utilizar funciones de

agregado ni subconsultas. El tipo de dato devuelto es int.

Si el número de valores devueltos por expresion es superior a 231-1, COUNT genera un error, en ese

caso hay que utilizar COUNT_BIG.


La palabra ALL indica que se tienen que tomar todos los valores de la columna, mientras que DISTINCT
hace que se consideren todas las repeticiones del mismo valor como uno solo. Estos parámetros son
opcionales, por defecto se considera ALL.



Por ejemplo:

SELECT COUNT(region) FROM oficinas;


Devuelve 9 porque tenemos nueve valores no nulos en la columna region.


SELECT COUNT(DISTINCT region) FROM oficinas;


Devuelve 4 porque tenemos nueve valores distintos, no nulos, en la columna región, los valores

repetidos los considera sólo una vez.

*
Si utilizamos * en vez de expresión, se cuentan todas las filas para devolver el número total de filas del

origen.

COUNT(*) no acepta parámetros y no se puede utilizar con DISTINCT. COUNT(*) no requiere un
parámetro expression porque, por definición, no utiliza información sobre ninguna columna específica. En el
recuento se incluyen las filas que contienen valores NULL.

SELECT COUNT(*) FROM empleados WHERE oficina=12;


Obtiene el número de empleados asignados a la oficina 12.


1.2.2 La función COUNT_BIG


Funciona igual que la función COUNT. La única diferencia entre ambas funciones está en los valores
devueltos, COUNT_BIG siempre devuelve un valor de tipo bigint y por lo tanto admite más valores de entrada,
no está limitado a 231-1 valores de entrada como COUNT.



1.2.3 La función MAX



MAX ([ALL|DISTINCT] expression)


Devuelve el valor máximo de la expresión sin considerar los nulos.


2

MAX se puede usar con columnas numéricas, de caracteres y de datetime, pero no con columnas de

bit. No se permiten funciones de agregado ni subconsultas.

Utilizar DISTINCT no tiene ningún sentido con MAX (el valor máximo será el mismo si consideramos las

repeticiones o no) y sólo se incluye para la compatibilidad con SQL-92.
1.2.4 La función MIN



MIN ([ALL|DISTINCT] expression)


Devuelve el valor mínimo de la expresión sin considerar los nulos.
MIN se puede usar con columnas numéricas, de caracteres y de datetime, pero no con columnas de bit.

No se permiten funciones de agregado ni subconsultas.

Utilizar DISTINCT no tiene ningún sentido con MIN (el valor mínimo será el mismo si consideramos las

repeticiones o no) y sólo se incluye para la compatibilidad con SQL-92.



1.2.5 La función SUM



SUM ([ALL|DISTINCT] expresion )


Devuelve la suma de los valores devueltos por la expresión.
Sólo puede utilizarse con columnas numéricas.
El resultado será del mismo tipo aunque puede tener una precisión mayor.


SELECT SUM(importe) FROM pedidos;


Obtiene el importe total vendido en todos los pedidos.


1.2.6 La función AVG



AVG ([ALL|DISTINCT] expresion )


Devuelve el promedio de los valores de un grupo, para calcular el promedio se omiten los valores nulos.

El grupo de valores lo determina el resultado de la expresión que será un nombre de columna o una

expresión basada en una columna o varias del origen de datos.

La función se aplica también a campos numéricos, y en este caso el tipo de dato del resultado puede

cambiar según las necesidades del sistema para representar el valor del resultado.
1.2.7 La función VAR

VAR ([ALL|DISTINCT] expresion )


Devuelve la varianza estadística de todos los valores de la expresión especificada.
VAR sólo se puede utilizar con columnas numéricas. Los valores NULL se pasan por alto.


1.2.8 La función VARP



VARP ([ALL|DISTINCT] expresion )


Devuelve la varianza estadística de la población para todos los valores de la expresión especificada.
Sólo se puede utilizar con columnas numéricas. Los valores NULL se pasan por alto.



3

1.2.9 La función STDEV



STDEV ([ALL|DISTINCT] expresion )


Devuelve la desviación típica estadística de todos los valores de la expresión especificada.
Sólo se puede utilizar con columnas numéricas. Los valores NULL se pasan por alto.


1.2.10 La función STDEVP



STDEVP ([ALL|DISTINCT] expresion )


Devuelve la desviación estadística estándar para la población de todos los valores de la expresión

especificada.

Sólo se puede utilizar con columnas numéricas. Los valores NULL se pasan por alto.


1.2.11 La función GROUPING



GROUPING (nb_columna)


Es una función de agregado que genera como salida una columna adicional con el valor 1 si la fila se
agrega mediante el operador CUBE o ROLLUP, o el valor 0 cuando la fila no es el resultado de CUBE o
ROLLUP.

Nb_columna tiene que ser una de las columnas de agrupación y la cláusula GROUP BY debe contener

el operador CUBE o ROLLUP.

En el siguiente punto, cuando veamos las cláusulas CUBE y ROLLUP quedará más claro.


1.3 Consultas agrupadas (cláusula GROUP BY).


Hasta ahora las consultas sumarias que hemos visto obtienen totales de todas las filas del origen y

producen una única fila de resultado.


Muchas veces cuando calculamos resúmenes nos interesan totales parciales, por ejemplo saber de cada
empleado cuánto ha vendido, y cuál ha sido su pedido máximo, de cada cliente cuándo fue la última vez que
nos compró, etc.

En todos estos casos en vez de obtener una fila única de resultados necesitamos una fila por cada

empleado, clientes, etc.


Podemos obtener estos subtotales con la cláusula GROUP BY.


GROUP BY [ ALL ] expresion_agrupacion [ ,...n ]
[ WITH { CUBE | ROLLUP } ]


Una consulta con una cláusula GROUP BY agrupa los datos de la tabla origen y produce una única fila
resultado por cada grupo formado. Las columnas indicadas en el GROUP BY se llaman columnas de
agrupación o agrupamiento .


Cuando queremos realizar una agrupación múltiple, por varias columnas, éstas se indican en la cláusula

GROUP BY en el orden de mayor a menor agrupación.


e