GROUPING_ID (Transact-SQL)

Es una función que calcula el nivel de agrupación. GROUPING_ID sólo se puede usar en la lista de <selección> SELECT, cláusulas HAVING u ORDER BY cuando se especifica GROUP BY.

Convenciones de sintaxis de Transact-SQL

Sintaxis

GROUPING_ID ( <column_expression>[ ,...n ] )

Argumentos

• <<expresiónDeColumna>>
  Es un argumento de tipo column_expression en una cláusula GROUP BY.

Tipo de valor devuelto

int

Notas

<expresiónDeColumna> de GROUPING_ID deben coincidir exactamente con la expresión en la lista GROUP BY. Por ejemplo, si agrupa por DATEPART (yyyy, <column name>), use GROUPING_ID (DATEPART (yyyy, <column name>)); o si agrupa por <column name>, use GROUPING_ID (<column name>).

Comparar GROUPING_ID() con GROUPING()

GROUPING_ID (<expresiónDeColumna> [ ,...n ]) ]) introduce el equivalente del valor devuelto de GROUPING (<expresiónDeColumna>) para cada columna de su lista de columnas en cada fila de salida como una cadena de unos y ceros. GROUPING_ID interpreta dicha cadena como un número de base 2 y devuelve el número entero equivalente. Por ejemplo, considere la siguiente instrucción: SELECT a, b, c, SUM(d),GROUPING_ID(a,b,c)FROM T GROUP BY <group by list>. La tabla siguiente muestra los valores de entrada y salida de GROUPING_ID().

Columnas agregadas	Entrada de GROUPING_ID (a, b, c) = GROUPING(a) + GROUPING(b) + GROUPING(c)	Resultado de GROUPING_ID()
a	100	4
b	010	2
c	001	1
ab	110	6
ac	101	5
bc	011	3
abc	111	7

Definición técnica de GROUPING_ID ()

Cada argumento de GROUPING_ID debe ser un elemento de la lista GROUP BY. GROUPING_ID () devuelve un mapa de bits de integer cuyos bits N más bajos pueden ser literales. Un bit literal indica que el argumento correspondiente no es una columna de agrupación en la fila de resultados determinada. El bit de menor orden corresponde al argumento N y el bit de menor orden de N-1ésimo corresponde al argumento 1.

Equivalentes de GROUPING_ID ()

En una consulta de agrupación única, GROUPING (<expresiónDeColumna>) equivale a GROUPING_ID (<expresiónDeColumna>) y ambos devuelven 0.

Por ejemplo, las siguientes instrucciones son equivalentes:

SELECT GROUPING_ID(A,B) FROM T GROUP BY CUBE(A,B)

SELECT 3 FROM T GROUP BY () UNION ALL SELECT 1 FROM T GROUP BY A UNION ALL SELECT 2 FROM T GROUP BY B UNION ALL SELECT 0 FROM T GROUP BY A,B

Ejemplos

A. Usar GROUPING_ID para identificar niveles de agrupación

El ejemplo siguiente devuelve el recuento de empleados por Name y Title, Name y total de la compañía. GROUPING_ID() se usa para crear un valor en cada fila de la columna Title que identifica su nivel de agregación.

USE AdventureWorks;
GO
SELECT D.Name
    ,CASE 
    WHEN GROUPING_ID(D.Name, E.Title) = 0 THEN E.Title
    WHEN GROUPING_ID(D.Name, E.Title) = 1 THEN N'Total: ' + D.Name 
    WHEN GROUPING_ID(D.Name, E.Title) = 3 THEN N'Company Total:'
        ELSE N'Unknown'
    END AS N'Title'
    ,COUNT(E.EmployeeID) AS N'Employee Count'
FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH
        ON E.EmployeeID = DH.EmployeeID
    INNER JOIN HumanResources.Department D
        ON D.DepartmentID = DH.DepartmentID     
WHERE DH.EndDate IS NULL
    AND D.DepartmentID IN (12,14)
GROUP BY ROLLUP(D.Name, E.Title);

B. Usar GROUPING_ID para filtrar un conjunto de resultados

Ejemplo sencillo

En el código siguiente, para devolver sólo las filas que tienen un recuento de empleados por título, quite los caracteres de comentario de HAVING GROUPING_ID(D.Name, E.Title); = 0. Para devolver sólo las filas que tienen un recuento de empleados por departamento, quite los caracteres de comentario de HAVING GROUPING_ID(D.Name, E.Title) = 1;.

USE AdventureWorks;
GO
SELECT D.Name
    ,E.Title
    ,GROUPING_ID(D.Name, E.Title) AS 'Grouping Level'
    ,COUNT(E.EmployeeID) AS N'Employee Count'
FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH
        ON E.EmployeeID = DH.EmployeeID
    INNER JOIN HumanResources.Department D
        ON D.DepartmentID = DH.DepartmentID     
WHERE DH.EndDate IS NULL
    AND D.DepartmentID IN (12,14)
GROUP BY ROLLUP(D.Name, E.Title)
--HAVING GROUPING_ID(D.Name, E.Title) = 0; --All titles
--HAVING GROUPING_ID(D.Name, E.Title) = 1; --Group by Name

A continuación se muestra el conjunto de resultados sin filtrar.

Name	Title	Grouping Level	Employee Count	Name
Document Control	Control Specialist	0	2	Document Control
Document Control	Document Control Assistant	0	2	Document Control
Document Control	Document Control Manager	0	1	Document Control
Document Control	NULL	1	5	Document Control
Facilities and Maintenance	Facilities Administrative Assistant	0	1	Facilities and Maintenance
Facilities and Maintenance	Facilities Manager	0	1	Facilities and Maintenance
Facilities and Maintenance	Janitor	0	4	Facilities and Maintenance
Facilities and Maintenance	Maintenance Supervisor	0	1	Facilities and Maintenance
Facilities and Maintenance	NULL	1	7	Facilities and Maintenance
NULL	NULL	3	12	NULL

Ejemplo complejo

En el ejemplo siguiente, GROUPING_ID() se usa para filtrar un conjunto de resultados que contiene varios niveles de agrupación por nivel de agrupación. Se puede usar un código similar para crear una vista con varios niveles de la agrupación y un procedimiento almacenado que llame a la vista con un parámetro que filtre la vista por nivel de agrupación.

USE AdventureWorks;
GO
DECLARE @Grouping nvarchar(50);
DECLARE @GroupingLevel smallint;
SET @Grouping = N'CountryRegionCode Total';

SELECT @GroupingLevel = (
    CASE @Grouping
        WHEN N'Grand Total'             THEN 15
        WHEN N'SalesPerson Total'       THEN 14
        WHEN N'Store Total'             THEN 13
        WHEN N'Store SalesPerson Total' THEN 12
        WHEN N'CountryRegionCode Total' THEN 11
        WHEN N'Group Total'             THEN 7
        ELSE N'Unknown'
    END);

SELECT 
    T.[Group]
    ,T.CountryRegionCode
    ,S.Name AS N'Store'
    ,(SELECT C.FirstName + ' ' + C.LastName 
        FROM Person.Contact C 
        WHERE C.ContactId = H.SalesPersonID)
        AS N'Sales Person'
    ,SUM(TotalDue)AS N'TotalSold'
    ,CAST(GROUPING(T.[Group])AS char(1)) + 
        CAST(GROUPING(T.CountryRegionCode)AS char(1)) + 
        CAST(GROUPING(S.Name)AS char(1)) + 
        CAST(GROUPING(H.SalesPersonID)AS char(1)) 
        AS N'GROUPING base-2'
    ,GROUPING_ID((T.[Group])
        ,(T.CountryRegionCode),(S.Name),(H.SalesPersonID)
        ) AS N'GROUPING_ID'
    ,CASE 
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 15 THEN N'Grand Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 14 THEN N'SalesPerson Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 13 THEN N'Store Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 12 THEN N'Store SalesPerson Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 11 THEN N'CountryRegionCode Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) =  7 THEN N'Group Total'
        ELSE N'Error'
        END AS N'Level'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
GROUP BY GROUPING SETS ((S.Name,H.SalesPersonID)
    ,(H.SalesPersonID),(S.Name)
    ,(T.[Group]),(T.CountryRegionCode),()
    )
HAVING GROUPING_ID(
    (T.[Group]),(T.CountryRegionCode),(S.Name),(H.SalesPersonID)
    ) = @GroupingLevel
ORDER BY 
    GROUPING_ID(S.Name,H.SalesPersonID),GROUPING_ID((T.[Group])
    ,(T.CountryRegionCode)
    ,(S.Name)
    ,(H.SalesPersonID))ASC;

C. Usar GROUPING_ID () con ROLLUP y CUBE para identificar niveles de agrupación

El código de los ejemplos siguientes muestra el uso de GROUPING() para calcular la columna Bit Vector(base-2). GROUPING_ID() se usa para calcular la columna Integer Equivalent correspondiente. El orden de columnas en la función GROUPING_ID() es el contrario del orden de las columnas que concatena la función GROUPING().

En estos ejemplos, GROUPING_ID() se usa para crear un valor para cada fila de la columna Grouping Level que identifica el nivel de agrupación. Los niveles de agrupación no son siempre una lista consecutiva de enteros que se inician con 1 (0, 1, 2,...n).

[!NOTA]

GROUPING y GROUPING_ID se pueden usar en una cláusula HAVING para filtrar un conjunto de resultados.

Ejemplo de ROLLUP

En este ejemplo, no aparecen todos los niveles de agrupación como lo hacen en el ejemplo de CUBE siguiente. Si se cambia el orden de las columnas en la lista de ROLLUP, los valores de nivel de la columna Grouping Level también se tendrán que cambiar.

USE AdventureWorks;
GO
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
    ,DATEPART(mm,OrderDate) AS N'Month'
    ,DATEPART(dd,OrderDate) AS N'Day'
    ,SUM(TotalDue) AS N'Total Due'
    ,CAST(GROUPING(DATEPART(dd,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(mm,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(yyyy,OrderDate))AS char(1)) 
     AS N'Bit Vector(base-2)'
    ,GROUPING_ID(DATEPART(yyyy,OrderDate)
        ,DATEPART(mm,OrderDate)
        ,DATEPART(dd,OrderDate)) 
        AS N'Integer Equivalent'
    ,CASE
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 0 THEN N'Year Month Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 1 THEN N'Year Month'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 2 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 3 THEN N'Year'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 4 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 5 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 6 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 7 THEN N'Grand Total'
    ELSE N'Error'
    END AS N'Grouping Level'
FROM Sales.SalesOrderHeader
WHERE DATEPART(yyyy,OrderDate) IN(N'2003',N'2004')
    AND DATEPART(mm,OrderDate) IN(1,2)
    AND DATEPART(dd,OrderDate) IN(1,2)
GROUP BY ROLLUP(DATEPART(yyyy,OrderDate)
        ,DATEPART(mm,OrderDate)
        ,DATEPART(dd,OrderDate))
ORDER BY GROUPING_ID(DATEPART(mm,OrderDate)
    ,DATEPART(yyyy,OrderDate)
    ,DATEPART(dd,OrderDate)
    )
    ,DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate);

A continuación se muestra un conjunto parcial de resultados.

Year	Month	Day	Total Due	Bit Vector (base-2)	Integer Equivalent	Grouping Level
2003	1	1	1762381	000	0	Year Month Day
2003	1	2	21772.35	000	0	Year Month Day
2003	2	1	3185233	000	0	Year Month Day
2003	2	2	21684.41	000	0	Year Month Day
2004	1	1	2239208	000	0	Year Month Day
2004	1	2	46458.07	000	0	Year Month Day
2004	2	1	3653194	000	0	Year Month Day
2004	2	2	54598.55	000	0	Year Month Day
2003	1	NULL	1784153	100	1	Year Month
2003	2	NULL	3206917	100	1	Year Month
2004	1	NULL	2285666	100	1	Year Month
2004	2	NULL	3707793	100	1	Year Month
2003	NULL	NULL	4991070	110	3	Year
2004	NULL	NULL	5993459	110	3	Year
NULL	NULL	NULL	10984529	111	7	Grand Total

Ejemplo de CUBE

En este ejemplo, la función GROUPING_ID() se usa para crear un valor para cada fila de la columna Grouping Level que identifica el nivel de agrupación.

A diferencia de ROLLUP en el ejemplo anterior, CUBE genera todos los niveles de agrupación. Si se cambia el orden de las columnas en la lista de CUBE, los valores de nivel de la columna Grouping Level también se tendrán que cambiar.

USE AdventureWorks;
GO
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
    ,DATEPART(mm,OrderDate) AS N'Month'
    ,DATEPART(dd,OrderDate) AS N'Day'
    ,SUM(TotalDue) AS N'Total Due'
    ,CAST(GROUPING(DATEPART(dd,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(mm,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(yyyy,OrderDate))AS char(1)) 
        AS N'Bit Vector(base-2)'
    ,GROUPING_ID(DATEPART(yyyy,OrderDate)
        ,DATEPART(mm,OrderDate)
        ,DATEPART(dd,OrderDate)) 
        AS N'Integer Equivalent'
    ,CASE
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 0 THEN N'Year Month Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 1 THEN N'Year Month'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 2 THEN N'Year Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 3 THEN N'Year'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 4 THEN N'Month Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 5 THEN N'Month'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 6 THEN N'Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 7 THEN N'Grand Total'
    ELSE N'Error'
    END AS N'Grouping Level'
FROM Sales.SalesOrderHeader
WHERE DATEPART(yyyy,OrderDate) IN(N'2003',N'2004')
    AND DATEPART(mm,OrderDate) IN(1,2)
    AND DATEPART(dd,OrderDate) IN(1,2)
GROUP BY CUBE(DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate))
ORDER BY GROUPING_ID(DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate)
    )
    ,DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate);

A continuación se muestra un conjunto parcial de resultados.

Year	Month	Day	Total Due	Bit Vector (base-2)	Integer Equivalent	Grouping Level
2003	1	1	1762381	000	0	Year Month Day
2003	1	2	21772.35	000	0	Year Month Day
2003	2	1	3185233	000	0	Year Month Day
2003	2	2	21684.41	000	0	Year Month Day
2004	1	1	2239208	000	0	Year Month Day
2004	1	2	46458.07	000	0	Year Month Day
2004	2	1	3653194	000	0	Year Month Day
2004	2	2	54598.55	000	0	Year Month Day
2003	1	NULL	1784153	100	1	Year Month
2003	2	NULL	3206917	100	1	Year Month
2004	1	NULL	2285666	100	1	Year Month
2004	2	NULL	3707793	100	1	Year Month
2003	NULL	1	4947613	010	2	Year Day
2003	NULL	2	43456.76	010	2	Year Day
2004	NULL	1	5892402	010	2	Year Day
2004	NULL	2	101056.6	010	2	Year Day
2003	NULL	NULL	4991070	110	3	Year
2004	NULL	NULL	5993459	110	3	Year
NULL	1	1	4001589	001	4	Month Day
NULL	1	2	68230.42	001	4	Month Day
NULL	2	1	6838427	001	4	Month Day
NULL	2	2	76282.96	001	4	Month Day
NULL	1	NULL	4069819	101	5	Month
NULL	2	NULL	6914710	101	5	Month
NULL	NULL	1	10840016	011	6	Day
NULL	NULL	2	144513.4	011	6	Day
NULL	NULL	NULL	10984529	111	7	Grand Total

Vea también

Referencia

GROUPING (Transact-SQL)

GROUP BY (Transact-SQL)

Conceptos

Utilizar GROUP BY con ROLLUP, CUBE y GROUPING SETS

Equivalentes de GROUPING SETS

Solucionar errores de GROUP BY