¿Cómo agrupar registros en SQL Server con la cláusula GROUP BY?

En este tutorial, aprenderá a usar la cláusula GROUP BY de SQL Server para organizar las filas en grupos por una o más columnas.

Introducción a la cláusula GROUP BY de SQL Server

La cláusula GROUP BY te permite organizar las filas de una consulta en grupos. Los grupos están determinados por las columnas que se especifican en la cláusula GROUP BY.

A continuación, se ilustra la sintaxis de la cláusula GROUP BY:

SELECT select_list FROM table_name
GROUP BY    column_name1,   column_name2 ,...;

En esta consulta, la cláusula GROUP BY regresa un grupo para cada combinación de los valores en las columnas enumeradas en la cláusula GROUP BY.

Veamos el siguiente ejemplo del uso de la cláusula GROUP BY con la tabla sales.orders de la base de datos de ejemplo:

SELECT customer_id, YEAR (order_date) order_year FROM sales.orders
WHERE  customer_id IN (1, 2)
ORDER BY customer_id;

Resultado:

customer_id	order_year
----------- ----------
1       	2016
1       	2018
1       	2018
2       	2017
2       	2017
2       	2018

En este ejemplo, recuperamos el id del cliente y el año que realizó una orden, siempre y cuando el Id del cliente es uno o dos, esto último con la ayuda de la cláusula WHERE y el resultado lo ordenamos con ORDER BY.

Como puedes ver claramente en el resultado, el cliente con la identificación uno realizó un pedido en 2016 y dos pedidos en 2018. El cliente con la identificación dos realizó dos pedidos en 2017 y un pedido en 2018.

Agreguemos una cláusula GROUP BY a la consulta para ver el efecto:

SELECT customer_id, YEAR (order_date) order_year FROM sales.orders
WHERE customer_id IN (1, 2)
GROUP BY customer_id, YEAR (order_date)
ORDER BY customer_id;

Resultado:

customer_id	order_year
-----------	----------
1       	2016
1       	2018
2       	2017
2       	2018

La cláusula GROUP BY organizó las primeras tres filas en dos grupos y las siguientes tres filas en los otros dos grupos con las combinaciones únicas del id del cliente y el año del pedido.

Hablando funcionalmente, la cláusula GROUP BY en la consulta anterior produjo el mismo resultado que la siguiente consulta que utiliza la cláusula DISTINCT:

SELECT DISTINCT customer_id, YEAR (order_date) order_year
FROM sales.orders
WHERE customer_id IN (1, 2)
ORDER BY customer_id;

Resultado:

customer_id	order_year
-----------	----------
1       	2016
1       	2018
2       	2017
2       	2018

La cláusula GROUP BY de SQL Server y funciones agregadas.

En la práctica, la cláusula GROUP BY a menudo se usa con funciones agregadas para generar informes resumidos.

Una función agregada realiza un cálculo en un grupo y devuelve un valor único por grupo. Por ejemplo, COUNT() devuelve el número de filas en cada grupo. Otras funciones agregadas comúnmente utilizadas son SUM(), AVG() (promedio), MIN() (mínimo), MAX() (máximo).

La cláusula GROUP BY organiza las filas en grupos y una función agregada devuelve el resumen (El total de registros, el valor mínimo, el valor máximo, el promedio, la suma, etc.) para cada grupo.

Por ejemplo, la siguiente consulta devuelve el número de pedidos realizados por el cliente por año:

SELECT customer_id, YEAR (order_date) order_year, COUNT (order_id) order_placed
FROM sales.orders
WHERE customer_id IN (1, 2)
GROUP BY customer_id, YEAR (order_date)
ORDER BY customer_id;

Resultado:

customer_id	order_year	order_placed
-----------	----------	------------
1       	2016    	1
1       	2018    	2
2       	2017    	2
2        	2018    	1

Si desea hacer referencia a cualquier columna o expresión que no figura en la cláusula GROUP BY, debe usar esa columna como entrada de una función agregada. De lo contrario, obtendrás un error porque no hay garantía de que la columna o expresión devolverá un solo valor por grupo. Por ejemplo, la siguiente consulta fallará:

SELECT customer_id, YEAR (order_date) order_year, order_status
FROM  sales.orders
WHERE customer_id IN (1, 2)
GROUP BY customer_id, YEAR (order_date)
ORDER BY customer_id;

Resultado:

Column 'sales.orders.order_status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Más ejemplos de la cláusula GROUP BY

Veamos algunos ejemplos más para comprender cómo funciona la cláusula GROUP BY.

Uso de la cláusula GROUP BY con la función COUNT()

La siguiente consulta devuelve el número de clientes en cada ciudad:

SELECT city, COUNT (customer_id) customer_count
FROM sales.customers
GROUP BY city
ORDER BY city;

Resultado:

city                                               customer_count
-------------------------------------------------- --------------
Albany                                             3
Amarillo                                           5
Amityville                                         9
Amsterdam                                          5
Anaheim                                            11
Apple Valley                                       11
Astoria                                            12
Atwater                                            5
Auburn                                             4
Bakersfield                                        5
Baldwin                                            7
.
.
.

En este ejemplo, la cláusula GROUP BY agrupa a los clientes por ciudad y la función COUNT() devuelve el número de clientes en cada ciudad.

Del mismo modo, la siguiente consulta devuelve el número de clientes por estado y ciudad.

SELECT city, state, COUNT (customer_id) customer_count
FROM sales.customers
GROUP BY state, city
ORDER BY city, state;

Resultado:

city                                               state                     customer_count
-------------------------------------------------- ------------------------- --------------
Albany                                             NY                        3
Amarillo                                           TX                        5
Amityville                                         NY                        9
Amsterdam                                          NY                        5
Anaheim                                            CA                        11
Apple Valley                                       CA                        11
Astoria                                            NY                        12
Atwater                                            CA                        5
Auburn                                             NY                        4
.
.
.

Uso de la cláusula GROUP BY con el ejemplo de las funciones MIN() y MAX()

La siguiente declaración devuelve los precios de lista mínimos y máximos de todos los productos de modelo 2018 y agrupados por marca:

SELECT brand_name, MIN (list_price) min_price, MAX (list_price) max_price
FROM production.products p
INNER JOIN production.brands b ON b.brand_id = p.brand_id
WHERE model_year = 2018
GROUP BY brand_name
ORDER BY brand_name;

Resultado:

brand_name         min_price                               max_price
--------------- --------------------------------------- ---------------------------------------
Electra           269.99                                  2999.99
Heller            2599.00                                 2599.00
Strider           89.99                                   289.99
Surly             469.99                                  2499.99
Trek              159.99                                  11999.99

(5 row(s) affected)

En este ejemplo, la cláusula WHERE siempre se procesa antes que la cláusula GROUP BY.

Uso de la cláusula GROUP BY con la función AVG()

La siguiente declaración utiliza la función AVG() para devolver el precio de lista promedio por marca para todos los productos con el año modelo 2018:

SELECT brand_name, AVG (list_price) avg_price
FROM production.products p
INNER JOIN production.brands b ON b.brand_id = p.brand_id
WHERE model_year = 2018
GROUP BY brand_name
ORDER BY brand_name;

Resultado:

brand_name        avg_price
-------------- ---------------------------------------
Electra           848.100111
Heller            2599.000000
Strider           209.990000
Surly             1502.457692
Trek               2464.990000

(5 row(s) affected)

Uso de la cláusula GROUP BY con la función SUM

Consulte la siguiente tabla order_items de la base de datos de ejemplo:

La siguiente consulta utiliza la función SUM() para obtener el valor neto de cada pedido:

SELECT order_id, SUM (quantity * list_price * (1 - discount)) net_value
FROM sales.order_items
GROUP BY order_id;

order_id    net_value
----------- ---------------
1           10231.0464
2           1697.9717
3           1519.9810
4           1349.9820
5           3900.0607
6           9442.5048
7           2165.0817
8           1372.4719
.
.
.

En este tutorial, has aprendido a usar la cláusula GROUP BY de SQL Server para organizar las filas en grupos por una lista específica de columnas.