Uso de funciones de agregado

3 minutos

T-SQL proporciona funciones de agregado como SUM, MAX y AVG para realizar cálculos que toman varios valores y devuelven un único resultado.

Uso de funciones de agregado

La mayoría de las consultas que hemos visto funcionan de fila en fila, mediante una cláusula WHERE para filtrar filas. Cada fila devuelta corresponde a una fila del conjunto de datos original.

Muchas funciones de agregado se proporcionan en SQL Server. En esta sección, veremos las funciones más comunes, como SUM, MIN, MAX, AVG y COUNT.

Al trabajar con funciones de agregado, debe tener en cuenta los siguientes puntos:

Las funciones de agregado devuelven un único valor (escalar) y se pueden usar en instrucciones SELECT casi en cualquier lugar en el que se pueda usar un solo valor. Por ejemplo, estas funciones se pueden usar en las cláusulas SELECT, HAVING y ORDER BY. Sin embargo, no se pueden usar en la cláusula WHERE.
Las funciones de agregado omiten los valores NULL, excepto cuando se usa COUNT(*).
Las funciones de agregado de una lista SELECT no tienen un encabezado de columna, a menos que proporcione un alias mediante AS.
Las funciones de agregado de una lista SELECT funcionan en todas las filas que se pasan a la operación SELECT. Si no hay ninguna cláusula GROUP BY, se resumirán todas las filas que cumplan cualquier filtro de la cláusula WHERE. Obtendrá más información sobre GROUP BY en la unidad siguiente.
A menos que use GROUP BY, no debe combinar funciones de agregado con columnas no incluidas en las funciones de la misma lista SELECT.

Para ampliar más allá de las funciones integradas, SQL Server proporciona un mecanismo para las funciones de agregado definidas por el usuario a través de Common Language Runtime (CLR) de .NET. Esta estrategia va más allá del ámbito de este módulo.

Funciones de agregado integradas

Como se mencionó, Transact-SQL proporciona muchas funciones de agregado integradas. Estas son algunas de las más comunes:

Nombre de la función

Sintaxis

Descripción

SUM

SUM(expression)

Suma todos los valores numéricos no NULL de una columna.

MEDIA

AVG(expression)

Promedia todos los valores numéricos no NULL de una columna (suma/recuento).

MÍN

MIN(expression)

Devuelve el número más pequeño, la fecha y hora más tempranas o la cadena que se produce por primera vez (según las reglas de ordenación de intercalación).

MÁX

MAX(expression)

Devuelve el número más grande, la fecha y hora más recientes o la última cadena (según las reglas de ordenación de intercalación).

COUNT o COUNT_BIG

COUNT(*) o COUNT(expresión)

Con (*), se cuentan todas las filas, incluidas las filas con valores NULL. Cuando se especifica una columna como expresión, devuelve el recuento de filas que no son NULL para esa columna. COUNT devuelve un valor int; COUNT_BIG devuelve un valor big_int.

Para usar un agregado integrado en una cláusula SELECT, observe el ejemplo siguiente en la base de datos de ejemplo MyStore:

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

Los resultados de esta consulta son similares a los siguientes:

AveragePrice

MinimumPrice

MaximumPrice

744,5952

2,2900

3578,2700

Tenga en cuenta que en el ejemplo anterior se resumen todas las filas de la tabla Production.Product. Podríamos modificar fácilmente la consulta para devolver los precios medios, mínimos y máximos de los productos de una categoría específica mediante la adición de una cláusula WHERE, como la siguiente:

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;

Cuando se usan agregados en una cláusula SELECT, todas las columnas a las que se hace referencia en la lista SELECT deben usarse como entradas para una función de agregado o se debe hacer referencia a ella en una cláusula GROUP BY.

Observe la siguiente consulta, que intenta incluir el campo ProductCategoryID en los resultados agregados:

SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

Esta consulta produce el siguiente error:

Mensaje 8120, Nivel 16, Estado 1, Línea 1

La columna "Production.ProductCategoryID" de la lista de selección no es válida, porque no está contenida en una función de agregado ni en la cláusula GROUP BY.

La consulta trata todas las filas como un único grupo agregado. Por lo tanto, todas las columnas deben usarse como entradas para agregar funciones.

En los ejemplos anteriores, agregamos datos numéricos como el precio y las cantidades en el ejemplo anterior. Algunas de las funciones de agregado también se pueden usar para resumir datos de fecha, hora y caracteres. En los ejemplos siguientes se muestra el uso de agregados con fechas y caracteres:

Esta consulta devuelve la primera y la última empresa por nombre, mediante MIN y MAX:

SELECT MIN(CompanyName) AS MinCustomer, 
       MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;

Esta consulta devolverá el primer y el último valor de CompanyName en la secuencia de intercalación de la base de datos, que en este caso es alfabético:

MinCustomer

MaxCustomer

Una tienda de bicicletas

Yellow Bicycle Company

Otras funciones se pueden anidar con funciones de agregado.

Por ejemplo, la función escalar YEAR se usa en el ejemplo siguiente para devolver solo la parte del año de la fecha de pedido, antes de que se evalúen MIN y MAX:

SELECT MIN(YEAR(OrderDate)) AS Earliest,
       MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;

Más antiguo

Más reciente

2008

2021

Las funciones MIN y MAX también se pueden usar con datos de fecha para devolver los valores cronológicos más antiguos y más recientes. Sin embargo, AVG y SUM solo se pueden usar para datos numéricos, lo que incluye tipos de datos enteros, money, float y decimal.

Uso de DISTINCT con funciones de agregado

Debe tener en cuenta el uso de DISTINCT en una cláusula SELECT para quitar filas duplicadas. Cuando se usa con una función de agregado, DISTINCT quita los valores duplicados de la columna de entrada antes de calcular el valor de resumen. DISTINCT es útil al resumir las apariciones únicas de valores, como los clientes en la tabla de pedidos.

En el ejemplo siguiente se devuelve el número de clientes que han realizado pedidos, independientemente del número de pedidos que han realizado:

SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;

COUNT(<una_columna>) simplemente cuenta cuántas filas tienen algún valor en la columna. Si no hay valores NULL, COUNT(<una_columna>) será igual que COUNT(*). COUNT (DISTINCT <una_columna>) cuenta cuántos valores diferentes hay en la columna.

Uso de funciones de agregado con NULL

Es importante tener en cuenta la posible presencia de valores NULL en los datos y cómo NULL interactúa con los componentes de consulta T-SQL, incluida la función de agregado. Hay algunas consideraciones que se deben tener en cuenta:

A excepción de COUNT que se usa con la opción (*), las funciones de agregado de T-SQL omiten los valores NULL. Por ejemplo, una función SUM solo agregará valores que no son NULL. Los valores NULL no se evalúan como cero. COUNT(*) cuenta todas las filas, independientemente del valor o no valor de cualquier columna.
La presencia de valores NULL en una columna puede dar lugar a cálculos inexactos de AVG, que sumarán solo las filas rellenadas y dividirán esa suma por el número de filas que no son NULL. Puede haber una diferencia en los resultados entre AVG(<columna>) y (SUM(<columna>)/COUNT(*)).

Observe, por ejemplo, la siguiente tabla llamada "t1":

NULL

Esta consulta muestra la diferencia entre cómo AVG controla NULL y cómo se podría calcular un promedio con una columna calculada SUM/COUNT(*):

SELECT SUM(c2) AS sum_nonnulls, 
    COUNT(*) AS count_all_rows, 
    COUNT(c2) AS count_nonnulls, 
    AVG(c2) AS average, 
    (SUM(c2)/COUNT(*)) AS arith_average
FROM t1;

El resultado sería:

sum_nonnulls

count_all_rows

count_nonnulls

average

arith_average

150

En este conjunto de resultados, la columna denominada average es el agregado que obtiene internamente la suma de 150 y divide por el recuento de valores no NULL de la columna c2. El cálculo sería 150/5 o 30. La columna denominada arith_average divide explícitamente la suma por el recuento de todas las filas, por lo que el cálculo es 150/6 o 25.

Si necesita resumir todas las filas, sean NULL o no, plantéese reemplazar los valores NULL por otro valor que la función de agregado no omitirá. Puede usar la función COALESCE para este fin.

Uso de funciones de agregado

Uso de funciones de agregado

Funciones de agregado integradas

Uso de DISTINCT con funciones de agregado

Uso de funciones de agregado con NULL

Comentarios