Compartir a través de


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

()

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

()

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

()

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