Resumir datos con GROUP BY

  • 3 minutos

Aunque las funciones de agregado son útiles para el análisis, recomendamos organizar los datos en subconjuntos antes de resumirlos. En esta sección aprenderá a hacerlo mediante la cláusula GROUP BY.

Uso de la cláusula GROUP BY

Como ha aprendido, cuando se procesa la instrucción SELECT, una vez que se han evaluado la cláusula FROM y la cláusula WHERE, se crea una tabla virtual. El contenido de la tabla virtual ahora está disponible para su posterior procesamiento. Puede usar la cláusula GROUP BY para subdividir el contenido de esta tabla virtual en grupos de filas.

Para agrupar filas, especifique uno o varios elementos en la cláusula GROUP BY:

GROUP BY <value1> [, <value2>, …]

GROUP BY crea grupos y coloca filas en cada grupo según lo determinado por los elementos especificados en la cláusula.

Por ejemplo, la consulta siguiente dará como resultado un conjunto de filas agrupadas, una fila por CustomerID en la tabla Sales.SalesOrderHeader. Otra manera de ver el proceso GROUP BY es que todas las filas con el mismo valor para CustomerID se agruparán y se devolverán en una sola fila de resultado.

SELECT CustomerID
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

La consulta anterior es equivalente a la consulta siguiente:

SELECT DISTINCT CustomerID
FROM Sales.SalesOrderHeader

Una vez procesada la cláusula GROUP BY y que cada fila se haya asociado a un grupo, las fases posteriores de la consulta deben agregar los elementos de las filas de origen que se encuentran en la lista SELECT, pero que no aparecen en la lista GROUP BY. Este requisito afectará a la forma de escribir las cláusulas SELECT y HAVING.

Por lo tanto, ¿cuál es la diferencia entre escribir la consulta con GROUP BY o DISTINCT? Si lo único que quiere saber son los distintos valores CustomerID, no hay ninguna diferencia. Pero con GROUP BY, podemos agregar otros elementos a la lista SELECT que, a continuación, se agregan para cada grupo.

La función de agregado más sencilla es COUNT(*). La consulta siguiente toma las 830 filas de origen originales de CustomerID y las agrupa en 89 grupos, en función de los valores CustomerID. Cada valor CustomerID distinto genera una fila de salida en la consulta GROUP BY.

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

Para cada valor CustomerID, la consulta agrega y cuenta las filas, por lo que el resultado nos muestra cuántas filas de la tabla SalesOrderHeader pertenecen a cada cliente.

CustomerID

OrderCount

1234

3

1005

1

Tenga en cuenta que GROUP BY no garantiza el orden de los resultados. A menudo, como resultado de la forma en que el procesador de consultas realiza la operación de agrupación, los resultados se devuelven en el orden de los valores de grupo. Sin embargo, no debe depender de este comportamiento. Si necesita ordenar los resultados, debe incluir explícitamente una cláusula ORDER:

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY CustomerID;

Esta vez, los resultados se devuelven en el orden especificado:

CustomerID

OrderCount

1005

1

1234

3

Las cláusulas de una instrucción SELECT se aplican en el orden siguiente:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

Los alias de columna se asignan en la cláusula SELECT, que se produce después de la cláusula GROUP BY, pero antes de la cláusula ORDER BY. Puede hacer referencia a un alias de columna en la cláusula ORDER BY, pero no en la cláusula GROUP BY. La consulta siguiente producirá un error de nombre de columna no válido:

SELECT CustomerID AS Customer,
       COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY Customer
ORDER BY Customer;

Sin embargo, la consulta siguiente se realizará correctamente, agrupando y ordenando los resultados por el identificador de cliente.

SELECT CustomerID AS Customer,
       COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY Customer;

Solución de errores de GROUP BY

Un obstáculo común para sentirse cómodo con el uso de GROUP BY en instrucciones SELECT es comprender por qué se produce el siguiente tipo de mensaje de error:

Mensaje 8120, Nivel 16, Estado 1, Línea 2 La columna <nombre_columna> no es válida en la lista de selección porque no está contenida en una función de agregado ni en una cláusula GROUP BY.

Por ejemplo, se permite la consulta siguiente porque cada columna de la lista SELECT es una columna de la cláusula GROUP BY o una función de agregado que funciona en cada grupo:

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

La consulta siguiente devolverá un error porque PurchaseOrderNumber no forma parte de GROUP BY y no se usa con una función de agregado.

SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

Esta consulta devuelve el error:

Msg 8120, Level 16, State 1, Line 1
Column 'Sales.SalesOrderHeader.PurchaseOrderNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Esta es otra manera de pensar en ello. Esta consulta devuelve una fila por cada valor CustomerID. Pero las filas del mismo valor CustomerID pueden tener valores PurchaseOrderNumber diferentes, por lo que ¿cuál de los valores es el que se debe devolver?

Si desea ver los pedidos por identificador de cliente y por pedido de compra, puede agregar la columna PurchaseOrderNumber a la cláusula GROUP BY, como se muestra a continuación:

SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, PurchaseOrderNumber;

Esta consulta devolverá una fila por cada cliente y cada combinación de pedido de compra, junto con el recuento de pedidos de esa combinación.