GROUP BY (Transact-SQL)

Agrupa un conjunto de filas seleccionado en un conjunto de filas de resumen de acuerdo con los valores de una o más columnas o expresiones en SQL Server 2012. Se devuelve una fila para cada grupo. Las funciones de agregado de la lista <select> de la cláusula SELECT proporcionan información de cada grupo en lugar de filas individuales.

La cláusula GROUP BY tiene una sintaxis que cumple la norma ISO y otra sintaxis que no cumple dicha norma. Solo se puede usar un estilo de sintaxis en cada instrucción SELECT. Use la sintaxis que cumple la norma ISO para todos los trabajos nuevos. La sintaxis que no cumple dicha norma solo se incluye por motivos de compatibilidad con versiones anteriores.

En este tema, una cláusula GROUP BY se puede describir como general o como simple:

• Una cláusula GROUP BY general incluye los elementos GROUPING SETS, CUBE, ROLLUP, WITH CUBE o WITH ROLLUP.

• Una cláusula GROUP BY simple no incluye GROUPING SETS, CUBE, ROLLUP, WITH CUBE ni WITH ROLLUP. GROUP BY (), total general, se considera una cláusula GROUP BY simple.

Convenciones de sintaxis de Transact-SQL (Transact-SQL)

Sintaxis

ISO-Compliant Syntax

GROUP BY <group by spec>

<group by spec> ::=
    <group by item> [ ,...n ]

<group by item> ::=
    <simple group by item>
    | <rollup spec>
    | <cube spec>
    | <grouping sets spec>
    | <grand total>

<simple group by item> ::=
    <column_expression>

<rollup spec> ::=
    ROLLUP ( <composite element list> ) 

<cube spec> ::=
    CUBE ( <composite element list> ) 

<composite element list> ::=
    <composite element> [ ,...n ]

<composite element> ::=
    <simple group by item>
    | ( <simple group by item list> ) 

<simple group by item list> ::=
    <simple group by item> [ ,...n ]

<grouping sets spec> ::=
    GROUPING SETS ( <grouping set list> ) 

<grouping set list> ::=
    <grouping set> [ ,...n ]

<grouping set> ::=
    <grand total>
    | <grouping set item>
    | ( <grouping set item list> ) 

<empty group> ::= 
        ( ) 

<grouping set item> ::=
    <simple group by item>
    | <rollup spec>
    | <cube spec>

<grouping set item list> ::=
    <grouping set item> [ ,...n ]

Non-ISO-Compliant Syntax
[ GROUP BY [ ALL ] group_by_expression [ ,...n ]
    [ WITH { CUBE | ROLLUP } ] 
]

Argumentos

• <column_expression>
  Es la expresión en la que se realiza la operación de agrupamiento.

• ROLLUP ( )
  Genera filas de agregado mediante la cláusula GROUP BY simple, más filas de subtotal o de superagregado, así como una fila de total general.

  El número de agrupaciones que se devuelve es igual al número de expresiones de la <lista de elementos compuestos> más uno. Considere, por ejemplo, la siguiente instrucción:

  SELECT a, b, c, SUM ( <expression> )
  FROM T
  GROUP BY ROLLUP (a,b,c);
  

  Se genera una fila con un subtotal para cada combinación única de valores de (a, b, c), (a, b) y (a). También se calcula una fila de total general.

  Las columnas se acumulan de derecha a izquierda. El orden de las columnas influye en las agrupaciones de salida de ROLLUP y también puede afectar al número de filas del conjunto de resultados.

• CUBE ( )
  Genera filas de agregado mediante la cláusula GROUP BY simple, filas de superagregado mediante la instrucción ROLLUP y filas de tabulación cruzada.

  CUBE genera una agrupación para todas las permutaciones de expresiones de la <lista de elementos compuestos>.

  El número de agrupaciones generado es igual a (2n), donde n es el número de expresiones de la <lista de elementos compuestos>. Considere, por ejemplo, la siguiente instrucción:

  SELECT a, b, c, SUM (<expression>)
  FROM T
  GROUP BY CUBE (a,b,c);
  

  Se genera una fila para cada combinación única de valores de (a, b, c), (a, b), (a, c), (b, c), (a), (b) y (c), con un subtotal para cada fila y una fila de total general.

  El orden de las columnas no afecta a la salida de CUBE.

• GROUPING SETS ( )
  Especifica varias agrupaciones de datos en una consulta. Solo se agregan los grupos especificados, en lugar del conjunto completo de agregaciones generado por CUBE o ROLLUP. Los resultados son equivalentes a usar la instrucción UNION ALL en los grupos especificados. GROUPING SETS puede contener un único elemento o una lista de elementos. GROUPING SETS puede especificar agrupaciones equivalentes a las devueltas por ROLLUP o CUBE. La <lista de elementos de conjunto de agrupación> puede contener ROLLUP o CUBE.

• ( )
  El grupo vacío genera un total.

Sintaxis que no cumple la norma ISO

• ALL
  Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan.Incluye todos los grupos y conjuntos de resultados, incluso aquellos en los que no hay filas que cumplan la condición de búsqueda especificada en la cláusula WHERE. Cuando se especifica ALL, se devuelven valores NULL para las columnas de resumen de los grupos que no cumplen la condición de búsqueda. No puede especificar ALL con los operadores CUBE y ROLLUP.

  GROUP BY ALL no se admite en consultas que tienen acceso a tablas remotas si también hay una cláusula WHERE en la consulta. GROUP BY ALL generará error en las columnas que tengan el atributo FILESTREAM.

• group_by_expression
  Es una expresión según la cual se realiza la agrupación. group_by_expression también se conoce como columna de agrupación. group_by expression puede ser una columna o una expresión no agregada que haga referencia a una columna devuelta por la cláusula FROM. Un alias de columna que esté definido en la lista SELECT no puede usarse para especificar una columna de agrupamiento.

  [!NOTA]

  Las columnas de tipo text, ntext e image no se pueden utilizar en group_by_expression.

  En las cláusulas GROUP BY que no contengan CUBE o ROLLUP, el número de elementos group_by_expression está limitado por los tamaños de columna de GROUP BY, las columnas de agregado y los valores de agregado que participan en la consulta. Este límite procede del límite de 8.060 bytes de la tabla de trabajo intermedia que se necesita para contener los resultados intermedios de la consulta. Se permite un máximo de 12 expresiones de agrupamiento cuando se especifica CUBE o ROLLUP.

  No se pueden especificar métodos de tipos de datos xml directamente en group_by_expression. En su lugar, haga referencia a una función definida por el usuario que incluya métodos de tipos de datos xml, o haga referencia a una columna calculada que los utilice.

• WITH CUBE
  Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan. Especifica que, además de las filas que normalmente proporciona GROUP BY, deben incluirse filas de resumen en el conjunto de resultados. Se devuelve una fila de resumen GROUP BY por cada posible combinación de grupo y subgrupo del conjunto de resultados. Utilice la función GROUPING para determinar si los valores NULL del conjunto de resultados son valores de resumen GROUP BY.

  El número de filas de resumen del conjunto de resultados se determina mediante el número de columnas que contiene la cláusula GROUP BY. Debido a que CUBE devuelve todas las combinaciones posibles de grupo y subgrupo, el número de filas es el mismo, independientemente del orden en que se especifiquen las columnas de agrupamiento.

• WITH ROLLUP
  Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan. Especifica que, además de las filas que normalmente proporciona GROUP BY, deben incluirse filas de resumen en el conjunto de resultados. Los grupos se resumen en un orden jerárquico, desde el nivel inferior del grupo hasta el superior. La jerarquía del grupo se determina por el orden en que se especifican las columnas de agrupamiento. Cambiar el orden de las columnas de agrupamiento puede afectar al número de filas generadas en el conjunto de resultados.

  Importante
  Cuando se utiliza CUBE o ROLLUP no se admiten agregados Distinct, como AVG (DISTINCT column_name), COUNT (DISTINCT column_name) y SUM (DISTINCT column_name). Si se utilizan, Motor de base de datos de SQL Server devuelve un mensaje de error y cancela la consulta.

Comentarios

Las expresiones de la cláusula GROUP BY pueden contener columnas de las tablas, de las tablas derivadas o de las vistas de la cláusula FROM. No es necesario que aparezcan las columnas en la lista de <selección> de la cláusula SELECT.

Deben incluirse en la lista GROUP BY todas las columnas de la tabla o la vista de cualquier expresión no agregada de la lista de <selección>:

• Están permitidas las siguientes instrucciones:

  SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA, ColumnB;
  SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA, ColumnB;
  SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA + ColumnB;
  SELECT ColumnA + ColumnB + constant FROM T GROUP BY ColumnA, ColumnB;
  

• No están permitidas las siguientes instrucciones:

  SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA + ColumnB;
  SELECT ColumnA + constant + ColumnB FROM T GROUP BY ColumnA + ColumnB;
  

Si se incluyen funciones de agregado en la <lista de selección> de la cláusula SELECT, GROUP BY calcula un valor de resumen para cada grupo. Se conocen como agregados vectoriales.

Las filas que no cumplen las condiciones especificadas en la cláusula WHERE se quitan antes de realizar ninguna operación de agrupación.

La cláusula HAVING se usa junto con la cláusula GROUP BY para filtrar los grupos en el conjunto de resultados.

La cláusula GROUP BY no ordena el conjunto de resultados. En su lugar, use la cláusula ORDER BY para ordenarlo.

Si una columna de agrupamiento contiene varios valores NULL, todos ellos se consideran equivalentes y se colocan en un grupo individual.

No es posible usar GROUP BY con un alias para reemplazar el nombre de una columna en la cláusula AS, a menos que dicho alias sustituya a un nombre de columna en una tabla derivada de la cláusula FROM.

Los conjuntos de agrupación duplicados de una lista GROUPING SETS no se eliminan. Los conjuntos de agrupación duplicados se pueden generar especificando una expresión de columna más de una vez o incluyendo una expresión de columna también generada por una instrucción CUBE o ROLLUP en la lista GROUPING SETS.

Cuando se utiliza ROLLUP, CUBE y GROUPING SETS se admiten agregados Distinct, como AVG (DISTINCT column_name), COUNT (DISTINCT column_name) y SUM (DISTINCT column_name).

ROLLUP, CUBE y GROUPING SETS no se pueden especificar en una vista indexada.

GROUP BY o HAVING no se pueden usar directamente en columnas de ntext, text o image. Estas columnas se pueden usar como argumentos en funciones que devuelven un valor de otro tipo de datos, como SUBSTRING() y CAST().

No se pueden especificar métodos de tipos de datos xml directamente en <column_expression>. En su lugar, haga referencia a una función definida por el usuario que incluya métodos de tipos de datos xml o haga referencia a una columna calculada que los utilice.

Limitaciones de GROUP BY para GROUPING SETS, ROLLUP y CUBE

Limitaciones de la sintaxis

Los operadores GROUPING SETS no se pueden usar en la cláusula GROUP BY a menos que formen parte de una lista GROUPING SETS. Por ejemplo, no se admite GROUP BY C1, (C2,..., Cn) pero sí GROUP BY GROUPING SETS (C1, (C2, ..., Cn)).

Los operadores GROUPING SETS no se pueden usar dentro de GROUPING SETS. Por ejemplo, no se admite GROUP BY GROUPING SETS (C1, GROUPING SETS (C2, C3)).

Las palabras clave que no cumplen la norma ISO ALL, WITH CUBE y WITH ROLLUP no se pueden usar en una cláusula GROUP BY con las palabras clave ROLLUP, CUBE o GROUPING SETS.

Limitaciones de tamaño

En la cláusula simple GROUP BY, no hay ningún límite en cuanto al número de expresiones.

En una cláusula GROUP BY que utilice ROLLUP, CUBE o GROUPING SETS, el número máximo de expresiones es 32, y el número máximo de conjuntos de agrupación que se pueden generar es 4.096 (212). Los ejemplos siguientes producen un error debido a que la cláusula GROUP BY es demasiado compleja:

• En los ejemplos siguientes se generan 8.192 (213) conjuntos de agrupamiento.

  GROUP BY CUBE (a1, ..., a13) 
  GROUP BY a1, ..., a13 WITH CUBE 
  

• En el ejemplo siguiente se generan 4.097 (212 + 1) conjuntos de agrupamiento.

  GROUP BY GROUPING SETS( CUBE(a1, ..., a12), b )
  

• En el ejemplo siguiente también se generan 4.097 (212 + 1) conjuntos de agrupamiento. Los conjuntos de agrupación CUBE () y () generan una fila de total general y los conjuntos de agrupación duplicados no se eliminan.

  GROUP BY GROUPING SETS( CUBE(a1, ..., a12), ())
  

Compatibilidad con las características GROUP BY de ISO y ANSI SQL-2006

En SQL Server 2012, la cláusula GROUP BY no puede contener una subconsulta en una expresión que se use para la lista de agrupación. Se devuelve el error 144.

SQL Server 2012 admite todas las características GROUP BY incluidas en la norma SQL-2006 con las excepciones de sintaxis siguientes:

• Los conjuntos de agrupamiento no se pueden usar en la cláusula GROUP BY a menos que formen parte de una lista GROUPING SETS explícita. Por ejemplo, GROUP BY Column1, (Column2, ...ColumnN) se admite en el estándar pero no en SQL Server. Se permite GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)) o GROUP BY Column1, Column2, ... ColumnN. Éstos son equivalentes semánticamente al ejemplo de GROUP BY anterior. Con ello se evita la posibilidad de que GROUP BY Column1, (Column2, ...ColumnN) se pueda malinterpretar como GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)). Éste no es equivalente semánticamente.

• No se pueden usar conjuntos de agrupamiento dentro de conjuntos de agrupamiento. Por ejemplo, GROUP BY GROUPING SETS (A1, A2,…An, GROUPING SETS (C1, C2, ...Cn)) se admite en el estándar SQL-2006 pero no en SQL Server. SQL Server 2012 permite GROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn ) o GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) ). Estos ejemplos son equivalentes semánticamente al primer ejemplo de GROUP BY y tienen una sintaxis más clara.

• No se admite GROUP BY [ALL/DISTINCT] en una cláusula GROUP BY general ni con las construcciones GROUPING SETS, ROLLUP, CUBE, WITH CUBE o WITH ROLLUP. ALL es el valor predeterminado y es implícito.

Comparación de las características GROUP BY compatibles

En la tabla siguiente se describen las características de GROUP BY que son compatibles dependiendo de la versión de SQL Server y del nivel de compatibilidad de la base de datos.

SELECT SUM (x)
FROM T 
GROUP BY dbo.cube(y); 

SELECT SUM (x)
FROM T 
GROUP BY dbo.cube(y); 

SELECT SUM (x)
FROM T 
GROUP BY [dbo].[cube](y);

SELECT SUM (x)
FROM T 
GROUP BY dbo.cube(y);

Ejemplos

A.Utilizar una cláusula GROUP BY simple

En el ejemplo siguiente se recupera el total de cada SalesOrderID de la tabla SalesOrderDetail.

USE AdventureWorks2012;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail AS sod
GROUP BY SalesOrderID
ORDER BY SalesOrderID;

B.Utilizar una cláusula GROUP BY con varias tablas

En el ejemplo siguiente se recupera el número de empleados de cada City de la tabla Address combinada con la tabla EmployeeAddress.

USE AdventureWorks2012;
GO
SELECT a.City, COUNT(bea.AddressID) EmployeeCount
FROM Person.BusinessEntityAddress AS bea 
    INNER JOIN Person.Address AS a
        ON bea.AddressID = a.AddressID
GROUP BY a.City
ORDER BY a.City;

C.Utilizar una cláusula GROUP BY con una expresión

En el ejemplo siguiente se recuperan las ventas totales de cada año con la función DATEPART. Debe incluirse la misma expresión en la lista SELECT y en la cláusula GROUP BY.

USE AdventureWorks2012;
GO
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
    ,SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy,OrderDate)
ORDER BY DATEPART(yyyy,OrderDate);

D.Utilizar una cláusula GROUP BY con una cláusula HAVING

En el ejemplo siguiente se usa la cláusula HAVING para especificar cuáles de los grupos generados en la cláusula GROUP BY deben incluirse en el conjunto de resultados.

USE AdventureWorks2012;
GO
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
    ,SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy,OrderDate)
HAVING DATEPART(yyyy,OrderDate) >= N'2003'
ORDER BY DATEPART(yyyy,OrderDate);

Vea también

Referencia

GROUPING_ID (Transact-SQL)

GROUPING (Transact-SQL)

SELECT (Transact-SQL)

SELECT (cláusula de Transact-SQL)