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.
