Utilizar GROUP BY con ROLLUP, CUBE y GROUPING SETS

Contenido de la comunidad

En esta sección

Estadísticas Anotaciones (0)

Haga clic para expandir

Contraer todo

Filtro de idioma

Contraer todoExpandir todo Código: Todos Código: Multiple Código: Visual Basic Código: C# Código: Visual C++ Código: F# Código: JScript

Utilizar GROUP BY con ROLLUP, CUBE y GROUPING SETS

Los operadores ROLLUP, CUBE y GROUPING SETS son extensiones de la cláusula GROUP BY. Los operadores ROLLUP, CUBE y GROUPING SETS pueden generar el mismo conjunto de resultados que al utilizar UNION ALL para combinar consultas de agrupación única; aunque utilizar uno de los operadores GROUP BY suele ser más eficaz.

El operador GROUPING SETS puede generar el mismo conjunto de resultados que el generado por medio de un operador GROUP BY, ROLLUP o CUBE simple. Cuando no se requieren todas las agrupaciones que se generan utilizando un operador ROLLUP o CUBE completo, se puede utilizar GROUPING SETS para especificar sólo las agrupaciones que se deseen. La lista GROUPING SETS puede contener agrupaciones duplicadas, y al utilizar GROUPING SETS con ROLLUP y CUBE, podría generar agrupaciones duplicadas. Las agrupaciones duplicadas se retienen igual que si se utilizara UNION ALL.

Nota
CUBE, ROLLUP y GROUPING SETS no admiten la función CHECKSUM_AGG.

Elementos compuestos y concatenados

Los grupos de columnas que están entre paréntesis internos en la lista GROUPING SETS son tratadas como un único conjunto. Por ejemplo, en la cláusula GROUP BY GROUPING SETS ((Colum1, Column2), Column3, Column4), Column1 y Column2 se tratan como una columna. Para obtener un ejemplo de cómo utilizar GROUPING SETS con elementos compuestos, vea el ejemplo H posterior en este tema.

Cuando la lista GROUPING SETS contiene varios conjuntos entre paréntesis internos, separados por comas, el resultado de los conjuntos se concatena. El conjunto de resultados es el producto cruzado o producto cartesiano de los conjuntos de agrupamiento. Para obtener un ejemplo de cómo utilizar GROUP BY con operaciones ROLLUP concatenadas, vea el ejemplo D más adelante en este tema.

ROLLUP y CUBE comparados con las dimensiones OLAP

Las consultas que usan los operadores ROLLUP y CUBE generan algunos de los conjuntos de resultados y realizan algunos de los cálculos que lleva a cabo las aplicaciones OLAP. El operador CUBE genera un conjunto de resultados que se puede utilizar en los informes de tabulación cruzada. Una operación ROLLUP puede calcular el equivalente de una dimensión o jerarquía OLAP.

Por ejemplo, dada una dimensión de tiempo con los niveles o atributos año, mes y día, la siguiente operación ROLLUP genera las agrupaciones siguientes.

Operación	Agrupaciones
ROLLUP (DATEPART(yyyy,OrderDate) ,DATEPART(mm,OrderDate) ,DATEPART(dd,OrderDate))	year, month, day year, month year ()

Operación

Agrupaciones

ROLLUP (DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate))

year, month, day

year, month

year

()

Dada una dimensión de ubicación con los niveles región y ciudad concatenada con los niveles de dimensión de tiempo año, mes y día, la siguiente operación ROLLUP genera las agrupaciones siguientes.

Operación	Agrupaciones
ROLLUP (region, city), ROLLUP (DATEPART(yyyy,OrderDate) ,DATEPART(mm,OrderDate) ,DATEPART(dd,OrderDate))	region, city, year, month, day region, city, year, month region, city, year region, city region, year, month, day region, year, month region, year region year, month, day year, month year ()

Operación

Agrupaciones

ROLLUP (region, city),
ROLLUP (DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate))

region, city, year, month, day

region, city, year, month

region, city, year

region, city

region, year, month, day

region, year, month

region, year

region

year, month, day

year, month

year

()

Una operación CUBE de los mismos niveles desde las dimensiones de ubicación y tiempo da como resultado las agrupaciones siguientes.

Operación	Agrupaciones
CUBE (region, city ,DATEPART(yyyy,OrderDate) ,DATEPART(mm,OrderDate) ,DATEPART(dd,OrderDate))	region, city, year, month, day region, city, year, month region, city, year region, city region, city, month, day region, city, month region, city, day region, city, year, day region, city, day region, year, month, day region, year, month region, year region, month, day region, month region, year, day region, day region city, year, month, day city, year, month city, year city, month, day city, month city, year, day city, day year, month, day year, month year year, day month, day month day ()

Operación

Agrupaciones

CUBE (region, city
    ,DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate))

region, city, year, month, day

region, city, year, month

region, city, year

region, city

region, city, month, day

region, city, month

region, city, day

region, city, year, day

region, city, day

region, year, month, day

region, year, month

region, year

region, month, day

region, month

region, year, day

region, day

region

city, year, month, day

city, year, month

city, year

city, month, day

city, month

city, year, day

city, day

year, month, day

year, month

year

year, day

month, day

month

day

()

NULL en conjuntos de resultados

En los conjuntos de resultados generados por los operadores GROUP BY, NULL tiene los usos siguientes:

Si una columna de agrupamiento contiene varios valores NULL, todos ellos se consideran equivalentes y se colocan en un grupo NULL individual.
Cuando se agrega una columna de forma consecutiva, el valor de la columna se muestra como NULL.

El ejemplo siguiente utiliza la función GROUPING para mostrar los dos usos de NULL. UNKNOWN reemplaza a NULL en filas en donde los valores nulos en una columna han sido agrupados. ALL reemplaza a NULL en una columna en donde NULL indica que se ha incluido una columna en una agregación.

USE tempdb;
GO
CREATE TABLE dbo.GroupingNULLS (
    Store nvarchar(19)
    ,SaleYear nvarchar(4)
    ,SaleMonth nvarchar (7))
INSERT INTO dbo.GroupingNULLS VALUES
(NULL,NULL,'January')
,(NULL,'2002',NULL)
,(NULL,NULL,NULL)
,('Active Cycling',NULL ,'January')
,('Active Cycling','2002',NULL)
,('Active Cycling',NULL ,NULL)
,('Active Cycling',NULL,'January')
,('Active Cycling','2003','Febuary')
,('Active Cycling','2003',NULL)
,('Mountain Bike Store','2002','January')
,('Mountain Bike Store','2002',NULL)
,('Mountain Bike Store',NULL,NULL)
,('Mountain Bike Store','2003','January')
,('Mountain Bike Store','2003','Febuary')
,('Mountain Bike Store','2003','March');

SELECT ISNULL(Store,
    CASE WHEN GROUPING(Store) = 0 THEN 'UNKNOWN' ELSE 'ALL' END)
    AS Store
    ,ISNULL(CAST(SaleYear AS nvarchar(7)),
    CASE WHEN GROUPING(SaleYear)= 0 THEN 'UNKNOWN' ELSE 'ALL' END)
    AS SalesYear
    ,ISNULL(SaleMonth,
    CASE WHEN GROUPING(SaleMonth) = 0 THEN 'UNKNOWN' ELSE 'ALL'END)
    AS SalesMonth
    ,COUNT(*) AS Count
FROM dbo.GroupingNULLS 
GROUP BY ROLLUP(Store, SaleYear, SaleMonth);

Éste es el conjunto de resultados.

Store	SalesYear	SalesMonth	Count
Desconocido	Desconocido	Desconocido	1
Desconocido	Desconocido	January	1
Desconocido	Desconocido	ALL	2
Desconocido	2002	Desconocido	1
Desconocido	2002	ALL	1
Desconocido	ALL	ALL	3
Active Cycling	Desconocido	Desconocido	1
Active Cycling	Desconocido	January	2
Active Cycling	Desconocido	ALL	3
Active Cycling	2002	Desconocido	1
Active Cycling	2002	ALL	1
Active Cycling	2003	Desconocido	1
Active Cycling	2003	Febuary	1
Active Cycling	2003	ALL	2
Active Cycling	ALL	ALL	6
Mountain Bike Store	Desconocido	Desconocido	1
Mountain Bike Store	Desconocido	ALL	1
Mountain Bike Store	2002	Desconocido	1
Mountain Bike Store	2002	January	1
Mountain Bike Store	2002	ALL	2
Mountain Bike Store	2003	Febuary	1
Mountain Bike Store	2003	January	1
Mountain Bike Store	2003	March	1
Mountain Bike Store	2003	ALL	3
Mountain Bike Store	ALL	ALL	6
ALL	ALL	ALL	15

Ejemplos

Los ejemplos de esta sección utilizan la función de agregado SUM para que se puedan comparar los conjuntos de resultados. También se podrían utilizar las otras funciones de agregado para calcular resúmenes diferentes.

A. Usar un GROUP BY simple

En el ejemplo siguiente, el GROUP BY simple devuelve un conjunto de resultados para comparar con los conjuntos de resultados de los ejemplos B a K. Estos ejemplos utilizan los operadores GROUP BY con la misma instrucción SELECT.

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
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
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID
ORDER BY T.[Group], T.CountryRegionCode
    ,S.Name,H.SalesPersonID;

Éste es el conjunto de resultados.

Region	Country	Store	SalesPersonID	Total Sales
Europe	DE	Versatile Sporting Goods Company	284	859.232
Europe	DE	Versatile Sporting Goods Company	289	17691.83
Europe	FR	Spa and Exercise Outfitters	284	32774.36
Europe	FR	Spa and Exercise Outfitters	286	246272.4

B. Usar GROUP BY ROLLUP

En el ejemplo siguiente, el operador ROLLUP devuelve un conjunto de resultados que contiene las agrupaciones siguientes:

Region, Country, Store y SalesPersonID.
Region, Country y Store
RegionRegion y Country. Country
Region
total general

El número de agrupaciones generadas por ROLLUP es igual al número de columnas en la lista ROLLUP más una agrupación de total general. El número de combinaciones únicas de valores en las columnas de la agrupación determina el número de filas en esa agrupación.

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales' 
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
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY ROLLUP(
    T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID)
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

Éste es el conjunto de resultados.

Region	Country	Store	SalesPersonID	Total Sales
NULL	NULL	NULL	NULL	297597.8
Europe	NULL	NULL	NULL	297597.8
Europe	DE	NULL	NULL	18551.07
Europe	DE	Versatile Sporting Goods Company	NULL	18551.07
Europe	DE	Versatile Sporting Goods Company	284	859.232
Europe	DE	Versatile Sporting Goods Company	289	17691.83
Europe	FR	NULL	NULL	279046.8
Europe	FR	Spa and Exercise Outfitters	NULL	279046.8
Europe	FR	Spa and Exercise Outfitters	284	32774.36
Europe	FR	Spa and Exercise Outfitters	286	246272.4

C. Usar GROUP BY ROLLUP con el orden de las columnas invertido

En el ejemplo siguiente, el operador ROLLUP devuelve un conjunto de resultados que contiene las agrupaciones siguientes:

SalesPersonID, Store, Country y Region.
SalesPersonID, Store y Country
SalesPersonIDSalesPersonID y Store. Store
SalesPersonID
total general

Las columnas de la lista ROLLUP son las mismas que las del ejemplo B, pero están en el orden inverso. Las columnas se resumen de derecha a izquierda; por consiguiente, el orden afecta a las agrupaciones. El número de filas en el conjunto de resultados podría variar en función del orden de las columnas.

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
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
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY ROLLUP(
    H.SalesPersonID, S.Name, T.CountryRegionCode, T.[Group])
ORDER BY H.SalesPersonID, S.Name, T.CountryRegionCode, T.[Group];

Éste es el conjunto de resultados.

Region	Country	Store	SalesPersonID	Total Sales
NULL	NULL	NULL	NULL	297597.8
NULL	NULL	NULL	284	33633.59
NULL	NULL	Spa and Exercise Outfitters	284	32774.36
NULL	FR	Spa and Exercise Outfitters	284	32774.36
Europe	FR	Spa and Exercise Outfitters	284	32774.36
NULL	NULL	Versatile Sporting Goods Company	284	859.232
NULL	DE	Versatile Sporting Goods Company	284	859.232
Europe	DE	Versatile Sporting Goods Company	284	859.232
NULL	NULL	NULL	286	246272.4
NULL	NULL	Spa and Exercise Outfitters	286	246272.4
NULL	FR	Spa and Exercise Outfitters	286	246272.4
Europe	FR	Spa and Exercise Outfitters	286	246272.4
NULL	NULL	NULL	289	17691.83
NULL	NULL	Versatile Sporting Goods Company	289	17691.83
NULL	DE	Versatile Sporting Goods Company	289	17691.83
Europe	DE	Versatile Sporting Goods Company	289	17691.83

D. Usar GROUP BY en operaciones ROLLUP concatenadas

En este ejemplo se obtiene el producto cruzado de las dos operaciones ROLLUP.

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,DATEPART(yyyy,OrderDate) AS 'Year'
    ,DATEPART(mm,OrderDate) AS 'Month'
    ,SUM(TotalDue) AS N'Total Sales'
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
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2004'
GROUP BY 
    ROLLUP(T.[Group], T.CountryRegionCode)
    ,ROLLUP(DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate))
ORDER BY T.[Group], T.CountryRegionCode
    ,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate);

Éste es el conjunto de resultados.

Region	Country	Year	Month	Total Sales
NULL	NULL	NULL	NULL	3031201
NULL	NULL	2004	NULL	3031201
NULL	NULL	2004	1	208553.6
NULL	NULL	2004	2	819466.6
NULL	NULL	2004	3	298579.1
NULL	NULL	2004	4	294427.7
NULL	NULL	2004	5	1070679
NULL	NULL	2004	6	339495.1
Europe	NULL	NULL	NULL	3031201
Europe	NULL	2004	NULL	3031201
Europe	NULL	2004	1	208553.6
Europe	NULL	2004	2	819466.6
Europe	NULL	2004	3	298579.1
Europe	NULL	2004	4	294427.7
Europe	NULL	2004	5	1070679
Europe	NULL	2004	6	339495.1
Europe	DE	NULL	NULL	1196260
Europe	DE	2004	NULL	1196260
Europe	DE	2004	1	155066.2
Europe	DE	2004	2	197801.8
Europe	DE	2004	3	180977.7
Europe	DE	2004	4	222683.4
Europe	DE	2004	5	258962
Europe	DE	2004	6	180769.1
Europe	FR	NULL	NULL	1834941
Europe	FR	2004	NULL	1834941
Europe	FR	2004	1	53487.37
Europe	FR	2004	2	621664.9
Europe	FR	2004	3	117601.4
Europe	FR	2004	4	71744.28
Europe	FR	2004	5	811716.9
Europe	FR	2004	6	158726

E. Usar GROUP BY CUBE

En el ejemplo siguiente, el operador CUBE devuelve un conjunto de resultados que tiene una agrupación para todas las combinaciones de columnas posibles de la lista CUBE y una agrupación de total general.

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
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
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY CUBE(
    T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID)
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

Éste es el conjunto de resultados.

Region	Country	Store	SalesPersonID	Total Sales
NULL	NULL	NULL	NULL	297597.8
NULL	NULL	NULL	284	33633.59
NULL	NULL	NULL	286	246272.4
NULL	NULL	NULL	289	17691.83
NULL	NULL	Spa and Exercise Outfitters	NULL	279046.8
NULL	NULL	Spa and Exercise Outfitters	284	32774.36
NULL	NULL	Spa and Exercise Outfitters	286	246272.4
NULL	NULL	Versatile Sporting Goods Company	NULL	18551.07
NULL	NULL	Versatile Sporting Goods Company	284	859.232
NULL	NULL	Versatile Sporting Goods Company	289	17691.83
NULL	DE	NULL	NULL	18551.07
NULL	DE	NULL	284	859.232
NULL	DE	NULL	289	17691.83
NULL	DE	Versatile Sporting Goods Company	NULL	18551.07
NULL	DE	Versatile Sporting Goods Company	284	859.232
NULL	DE	Versatile Sporting Goods Company	289	17691.83
NULL	FR	NULL	NULL	279046.8
NULL	FR	NULL	284	32774.36
NULL	FR	NULL	286	246272.4
NULL	FR	Spa and Exercise Outfitters	NULL	279046.8
NULL	FR	Spa and Exercise Outfitters	284	32774.36
NULL	FR	Spa and Exercise Outfitters	286	246272.4
Europe	NULL	NULL	NULL	297597.8
Europe	NULL	NULL	284	33633.59
Europe	NULL	NULL	286	246272.4
Europe	NULL	NULL	289	17691.83
Europe	NULL	Spa and Exercise Outfitters	NULL	279046.8
Europe	NULL	Spa and Exercise Outfitters	284	32774.36
Europe	NULL	Spa and Exercise Outfitters	286	246272.4
Europe	NULL	Versatile Sporting Goods Company	NULL	18551.07
Europe	NULL	Versatile Sporting Goods Company	284	859.232
Europe	NULL	Versatile Sporting Goods Company	289	17691.83
Europe	DE	NULL	NULL	18551.07
Europe	DE	NULL	284	859.232
Europe	DE	NULL	289	17691.83
Europe	DE	Versatile Sporting Goods Company	NULL	18551.07
Europe	DE	Versatile Sporting Goods Company	284	859.232
Europe	DE	Versatile Sporting Goods Company	289	17691.83
Europe	FR	NULL	NULL	279046.8
Europe	FR	NULL	284	32774.36
Europe	FR	NULL	286	246272.4
Europe	FR	Spa and Exercise Outfitters	NULL	279046.8
Europe	FR	Spa and Exercise Outfitters	284	32774.36
Europe	FR	Spa and Exercise Outfitters	286	246272.4

F. Usar CUBE con elementos compuestos

En el ejemplo siguiente, el operador CUBE devuelve un conjunto de resultados que tiene una agrupación para todas las posibles combinaciones de columnas en la lista CUBE y una agrupación de total general.

El operador procesa cada una de las columnas agrupadas (T.[Group], T.CountryRegionCode) y (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)) como una sola columna.

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,DATEPART(yyyy,OrderDate) AS 'Year'
    ,DATEPART(mm,OrderDate) AS 'Month'
    ,SUM(TotalDue) AS N'Total Sales'
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
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2004'
GROUP BY CUBE(
    (T.[Group], T.CountryRegionCode)
    ,(DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)))
ORDER BY T.[Group], T.CountryRegionCode
    ,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate);

Éste es el conjunto de resultados.

Region	Country	Year	Month	Total Sales
NULL	NULL	NULL	NULL	3031201
NULL	NULL	2004	1	208553.6
NULL	NULL	2004	2	819466.6
NULL	NULL	2004	3	298579.1
NULL	NULL	2004	4	294427.7
NULL	NULL	2004	5	1070679
NULL	NULL	2004	6	339495.1
Europe	DE	NULL	NULL	1196260
Europe	DE	2004	1	155066.2
Europe	DE	2004	2	197801.8
Europe	DE	2004	3	180977.7
Europe	DE	2004	4	222683.4
Europe	DE	2004	5	258962
Europe	DE	2004	6	180769.1
Europe	FR	NULL	NULL	1834941
Europe	FR	2004	1	53487.37
Europe	FR	2004	2	621664.9
Europe	FR	2004	3	117601.4
Europe	FR	2004	4	71744.28
Europe	FR	2004	5	811716.9
Europe	FR	2004	6	158726

G. Usar GROUP BY con GROUPING SETS

En el ejemplo siguiente, el operador GROUPING SETS tiene cuatro agrupaciones, una para cada columna de la lista SELECT. El operador devuelve una fila por cada valor único en las columnas Region, Country, Store y SalesPersonID .

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
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
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS
    (T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID)
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

Éste es el conjunto de resultados.

Region	Country	Store	SalesPersonID	Total Sales
NULL	NULL	NULL	284	33633.59
NULL	NULL	NULL	286	246272.4
NULL	NULL	NULL	289	17691.83
NULL	NULL	Spa and Exercise Outfitters	NULL	279046.8
NULL	NULL	Versatile Sporting Goods Company	NULL	18551.07
NULL	DE	NULL	NULL	18551.07
NULL	FR	NULL	NULL	279046.8
Europe	NULL	NULL	NULL	297597.8

H. Usar GROUPING SETS con elementos compuestos

En el ejemplo siguiente, la lista GROUPING SETS contiene dos elementos compuestos, (T.[Group], T.CountryRegionCode) y (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)). Cada elemento compuesto se trata como una columna.

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,DATEPART(yyyy,OrderDate) AS 'Year'
    ,DATEPART(mm,OrderDate) AS 'Month'
    ,SUM(TotalDue) AS N'Total Sales'
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
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2004'
GROUP BY GROUPING SETS(
    (T.[Group], T.CountryRegionCode)
    ,(DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)))
ORDER BY T.[Group], T.CountryRegionCode
    ,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate);

Éste es el conjunto de resultados.

Region	Country	Year	Month	Total Sales
NULL	NULL	2004	1	208553.6
NULL	NULL	2004	2	819466.6
NULL	NULL	2004	3	298579.1
NULL	NULL	2004	4	294427.7
NULL	NULL	2004	5	1070679
NULL	NULL	2004	6	339495.1
Europe	DE	NULL	NULL	1196260
Europe	FR	NULL	NULL	1834941

I. Usar GROUP BY con múltiples GROUPING SETS

En el ejemplo siguiente, la lista GROUPING SETS tiene cinco elementos. El conjunto de resultados tiene una fila para los elementos siguientes:

Cada combinación única de valores en las columnas Region y Country
Cada valor único en la columna Store
Cada combinación única de valores en las columnas SalesPersonID y Region
Cada valor único en la columna SalesPersonID
Un total general

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
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
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
    (T.[Group], T.CountryRegionCode)
    ,(S.Name)
    ,(H.SalesPersonID,T.[Group])
    ,(H.SalesPersonID)
    ,())
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

Éste es el conjunto de resultados.

Region	Country	Store	SalesPersonID	Total Sales
NULL	NULL	NULL	NULL	297597.8
NULL	NULL	NULL	284	33633.59
NULL	NULL	NULL	286	246272.4
NULL	NULL	NULL	289	17691.83
NULL	NULL	Spa and Exercise Outfitters	NULL	279046.8
NULL	NULL	Versatile Sporting Goods Company	NULL	18551.07
Europe	NULL	NULL	284	33633.59
Europe	NULL	NULL	286	246272.4
Europe	NULL	NULL	289	17691.83
Europe	DE	NULL	NULL	18551.07
Europe	FR	NULL	NULL	279046.8

J. Usar GROUPING SETS con un ROLLUP de parte de la lista GROUP BY

En el ejemplo siguiente, la lista GROUPING SETS incluye agrupaciones para las columnas T.[Group] y T.CountryRegionCode y un ROLLUP de las columnas S.Name y H.SalesPersonID.

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
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
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
    T.[Group], T.CountryRegionCode
   ,ROLLUP(S.Name, H.SalesPersonID))
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

Éste es el conjunto de resultados.

Region	Country	Store	SalesPersonID	Total Sales
NULL	NULL	NULL	NULL	297597.8
NULL	NULL	Spa and Exercise Outfitters	NULL	279046.8
NULL	NULL	Spa and Exercise Outfitters	284	32774.36
NULL	NULL	Spa and Exercise Outfitters	286	246272.4
NULL	NULL	Versatile Sporting Goods Company	NULL	18551.07
NULL	NULL	Versatile Sporting Goods Company	284	859.232
NULL	NULL	Versatile Sporting Goods Company	289	17691.83
NULL	DE	NULL	NULL	18551.07
NULL	FR	NULL	NULL	279046.8
Europe	NULL	NULL	NULL	297597.8

K. Usar GROUPING SETS con un CUBE de parte de la lista GROUP BY

En el ejemplo siguiente, la lista GROUPING SETS incluye agrupaciones para las columnas T.[Group] y T.CountryRegionCode y un CUBE de las columnas S.Name y H.SalesPersonID.

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
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
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
    T.[Group], T.CountryRegionCode
    ,CUBE(S.Name, H.SalesPersonID))
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

Éste es el conjunto de resultados.

Region	Country	Store	SalesPersonID	Total Sales
NULL	NULL	NULL	NULL	297597.8
NULL	NULL	NULL	284	33633.59
NULL	NULL	NULL	286	246272.4
NULL	NULL	NULL	289	17691.83
NULL	NULL	Spa and Exercise Outfitters	NULL	279046.8
NULL	NULL	Spa and Exercise Outfitters	284	32774.36
NULL	NULL	Spa and Exercise Outfitters	286	246272.4
NULL	NULL	Versatile Sporting Goods Company	NULL	18551.07
NULL	NULL	Versatile Sporting Goods Company	284	859.232
NULL	NULL	Versatile Sporting Goods Company	289	17691.83
NULL	DE	NULL	NULL	18551.07
NULL	FR	NULL	NULL	279046.8
Europe	NULL	NULL	NULL	297597.8

Vea también

Referencia

GROUP BY (Transact-SQL)

GROUPING (Transact-SQL)

GROUPING_ID (Transact-SQL)

Conceptos

Equivalentes de GROUPING SETS

Solucionar errores de GROUP BY

Etiquetas

: Agregar una etiqueta

Contenido de la comunidad

Agregar contenido nuevo

Anotaciones

Administre su perfil | Contacte con nosotros | Boletín