Condividi tramite


Utilizzo della clausola GROUP BY con gli operatori ROLLUP, CUBE e GROUPING SETS

Gli operatori ROLLUP, CUBE e GROUPING SETS sono estensioni della clausola GROUP BY. Gli operatori ROLLUP, CUBE o GROUPING SETS possono generare lo stesso set di risultati che si ottiene utilizzando UNION ALL per combinare le singole query di raggruppamento. Tuttavia, l'utilizzo di uno degli operatori GROUP BY è di solito più efficace.

L'operatore GROUPING SETS può generare lo stesso set di risultati generato utilizzando un semplice operatore GROUP BY, ROLLUP o CUBE. Quando non sono richiesti tutti i raggruppamenti generati utilizzando un operatore ROLLUP o CUBE completo, è possibile utilizzare GROUPING SETS per specificare solo i raggruppamenti che si desidera. L'elenco GROUPING SETS può contenere raggruppamenti duplicati e, quando viene utilizzato con ROLLUP e CUBE, potrebbe generare raggruppamenti duplicati. I raggruppamenti duplicati vengono mantenuti allo stesso modo in cui avviene utilizzando UNION ALL.

Nota

CUBE, ROLLUP e GROUPING SETS non supportano la funzione CHECKSUM_AGG.

Elementi composti e concatenati

Più colonne racchiuse nelle parentesi interne nell'elenco GROUPINGSETS vengono considerate un singolo set. Ad esempio, nella clausola GROUP BY GROUPING SETS ((Colum1, Column2), Column3, Column4), Column1 e Column2 vengono considerati come una sola colonna. Per un esempio sull'utilizzo di GROUPING SETS con elementi composti, vedere l'esempio H più avanti in questo argomento.

Quando l'elenco GROUPING SETS contiene più set in parentesi interne, separati da virgole, l'output dei set è concatenato. Il set di risultati è il prodotto incrociato o prodotto cartesiano dei set di raggruppamento. Per un esempio sull'utilizzo di GROUP BY con le operazioni ROLLUP concatenate, vedere l'esempio D più avanti in questo argomento.

ROLLUP e CUBE confrontati con le dimensioni OLAP

Le query che utilizzano gli operatori ROLLUP e CUBE generano alcuni degli stessi set di risultati ed eseguono alcuni degli stessi calcoli delle applicazioni OLAP. L'operatore CUBE genera un set di risultati che può essere utilizzato per report a tabulazione incrociata. Un'operazione ROLLUP è in grado di calcolare l'equivalente di una dimensione o gerarchia OLAP.

Ad esempio, dopo aver assegnato una dimensione temporale con i livelli o gli attributi di anno, mese e giorno, la seguente operazione ROLLUP genera i raggruppamenti seguenti.

Operazione

Raggruppamenti

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

year, month, day

year, month

year

()

Ad esempio, dopo aver assegnato una dimensione di località con i livelli regione e città concatenati con i livelli di dimensione temporale anno, mese e giorno, la seguente operazione ROLLUP genera i raggruppamenti seguenti.

Operazione

Raggruppamenti

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

()

Un'operazione CUBE con gli stessi livelli delle dimensioni temporali e di località genera i raggruppamenti seguenti.

Operazione

Raggruppamento

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 nei set di risultati

Nei set di risultati generati dagli operatori GROUP BY, NULL viene utilizzato come segue:

  • Se una colonna di raggruppamento contiene NULL, tutti i valori Null vengono considerati uguali e inseriti in un unico gruppo NULL.

  • Quando una colonna viene aggregata in una riga, il valore della colonna viene visualizzato come NULL.

Nel seguente esempio viene utilizzata la funzione GROUPING per mostrare i due utilizzi di NULL. UNKNOWN sostituisce NULL nelle righe in cui i valori Null di una colonna sono stati raggruppati. ALL sostituisce NULL in una colonna in cui NULL indica che una colonna è stata inclusa in un'aggregazione.

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);

Set di risultati:

Store

SalesYear

SalesMonth

Count

Sconosciuto

Sconosciuto

Sconosciuto

1

Sconosciuto

Sconosciuto

Gennaio

1

Sconosciuto

Sconosciuto

ALL

2

Sconosciuto

2002

Sconosciuto

1

Sconosciuto

2002

ALL

1

Sconosciuto

ALL

ALL

3

Ciclo attivo

Sconosciuto

Sconosciuto

1

Ciclo attivo

Sconosciuto

Gennaio

2

Ciclo attivo

Sconosciuto

ALL

3

Ciclo attivo

2002

Sconosciuto

1

Ciclo attivo

2002

ALL

1

Ciclo attivo

2003

Sconosciuto

1

Ciclo attivo

2003

Febbraio

1

Ciclo attivo

2003

ALL

2

Ciclo attivo

ALL

ALL

6

Punto vendita Mountain Bike

Sconosciuto

Sconosciuto

1

Punto vendita Mountain Bike

Sconosciuto

ALL

1

Punto vendita Mountain Bike

2002

Sconosciuto

1

Punto vendita Mountain Bike

2002

Gennaio

1

Punto vendita Mountain Bike

2002

ALL

2

Punto vendita Mountain Bike

2003

Febbraio

1

Punto vendita Mountain Bike

2003

Gennaio

1

Punto vendita Mountain Bike

2003

Marzo

1

Punto vendita Mountain Bike

2003

ALL

3

Punto vendita Mountain Bike

ALL

ALL

6

ALL

ALL

ALL

15

ESEMPI

Negli esempi di questa sezione viene utilizzata la funzione di aggregazione SUM, in modo che i set di risultati possano essere confrontati. Possono anche essere utilizzate le altre funzioni di aggregazione per calcolare diversi riepiloghi.

A. Utilizzo di un'istruzione GROUP BY semplice

Nell'esempio seguente, l'istruzione semplice GROUP BY restituisce un set di risultati da confrontare con i set di risultati degli esempi da B a K. In questi esempi vengono utilizzati gli operatori GROUP BY con la stessa istruzione SELECT.

USE AdventureWorks2008R2;
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.StoreID  = S.BusinessEntityID
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 290, 288)
    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;

Set di risultati:

Region

Country

Store

SalesPersonID

Total Sales

Europe

DE

Azienda di merci sportive versatili

284

859.232

Europe

DE

Azienda di merci sportive versatili

289

17691.83

Europe

FR

Fornitori di prodotti sportivi e per il benessere

284

32774.36

Europe

FR

Fornitori di prodotti sportivi e per il benessere

286

246272.4

B. Utilizzo di GROUP BY ROLLUP

Nell'esempio seguente, l'operatore ROLLUP restituisce un set di risultati che contiene i raggruppamenti seguenti:

  • Region, Country, Store e SalesPersonID

  • Region, Country e Store

  • Regione Country

  • Region

  • totale complessivo

Il numero di raggruppamenti generato da ROLLUP è lo stesso numero delle colonne nell'elenco ROLLUP più un raggruppamento totale complessivo. Il numero di righe in un raggruppamento è determinato dal numero di combinazioni univoche di valori nelle colonne del raggruppamento.

USE AdventureWorks2008R2;
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.StoreID  = S.BusinessEntityID
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 290, 288)
    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;

Set di risultati:

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

Azienda di merci sportive versatili

NULL

18551.07

Europe

DE

Azienda di merci sportive versatili

284

859.232

Europe

DE

Azienda di merci sportive versatili

289

17691.83

Europe

FR

NULL

NULL

279046.8

Europe

FR

Fornitori di prodotti sportivi e per il benessere

NULL

279046.8

Europe

FR

Fornitori di prodotti sportivi e per il benessere

284

32774.36

Europe

FR

Fornitori di prodotti sportivi e per il benessere

286

246272.4

C. Utilizzo di GROUP BY ROLLUP con l'ordine delle colonne invertito

Nell'esempio seguente, l'operatore ROLLUP restituisce un set di risultati che contiene i raggruppamenti seguenti:

  • SalesPersonID, Store, Country e Region

  • SalesPersonID, Store e Country

  • SalesPersonID e Store

  • SalesPersonID

  • totale complessivo

Le colonne dell'elenco ROLLUP sono le stesse dell'esempio B, ma nell'ordine opposto. Le colonne sono raccolte da destra a sinistra, quindi l'ordine interessa i raggruppamenti. Il numero di righe nel set di risultati potrebbe variare con l'ordine delle colonne.

USE AdventureWorks2008R2;
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.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 290, 288)
    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];

Set di risultati:

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

NULL

297597.8

NULL

NULL

NULL

284

33633.59

NULL

NULL

Fornitori di prodotti sportivi e per il benessere

284

32774.36

NULL

FR

Fornitori di prodotti sportivi e per il benessere

284

32774.36

Europe

FR

Fornitori di prodotti sportivi e per il benessere

284

32774.36

NULL

NULL

Azienda di merci sportive versatili

284

859.232

NULL

DE

Azienda di merci sportive versatili

284

859.232

Europe

DE

Azienda di merci sportive versatili

284

859.232

NULL

NULL

NULL

286

246272.4

NULL

NULL

Fornitori di prodotti sportivi e per il benessere

286

246272.4

NULL

FR

Fornitori di prodotti sportivi e per il benessere

286

246272.4

Europe

FR

Fornitori di prodotti sportivi e per il benessere

286

246272.4

NULL

NULL

NULL

289

17691.83

NULL

NULL

Azienda di merci sportive versatili

289

17691.83

NULL

DE

Azienda di merci sportive versatili

289

17691.83

Europe

DE

Azienda di merci sportive versatili

289

17691.83

D. Utilizzo di GROUP BY con le operazioni ROLLUP concatenate

Nell'esempio seguente viene restituito il prodotto incrociato delle due operazioni ROLLUP.

USE AdventureWorks2008R2;
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.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2006'
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);

Set di risultati:

Region

Country

Year

Month

Total Sales

NULL

NULL

NULL

NULL

966221.9606

NULL

NULL

2006

NULL

966221.9606

NULL

NULL

2006

7

109936.0248

NULL

NULL

2006

8

296651.4808

NULL

NULL

2006

9

184477.7563

NULL

NULL

2006

10

62792.5455

NULL

NULL

2006

11

213238.0125

NULL

NULL

2006

12

99126.1407

Europe

NULL

NULL

NULL

966221.9606

Europe

NULL

2006

NULL

966221.9606

Europe

NULL

2006

7

109936.0248

Europe

NULL

2006

8

296651.4808

Europe

NULL

2006

9

184477.7563

Europe

NULL

2006

10

62792.5455

Europe

NULL

2006

11

213238.0125

Europe

NULL

2006

12

99126.1407

Europe

FR

NULL

NULL

966221.9606

Europe

FR

2006

NULL

966221.9606

Europe

FR

2006

7

109936.0248

Europe

FR

2006

8

296651.4808

Europe

FR

2006

9

184477.7563

Europe

FR

2006

10

62792.5455

Europe

FR

2006

11

213238.0125

Europe

FR

2006

12

99126.1407

E. Utilizzo di GROUP BY CUBE

Nell'esempio seguente l'operatore CUBE restituisce un set di risultati che include un raggruppamento per tutte le possibili combinazioni di colonne nell'elenco CUBE e un raggruppamento per il totale complessivo.

USE AdventureWorks2008R2;
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 AS C
    INNER JOIN Sales.Store AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    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;

Set di risultati:

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

NULL

254013.6014

NULL

NULL

NULL

287

28461.1854

NULL

NULL

NULL

288

17073.0655

NULL

NULL

NULL

290

208479.3505

NULL

NULL

Fornitori di prodotti sportivi e per il benessere

NULL

236210.9015

NULL

NULL

Fornitori di prodotti sportivi e per il benessere

287

27731.551

NULL

NULL

Fornitori di prodotti sportivi e per il benessere

290

208479.3505

NULL

NULL

Azienda di merci sportive versatili

NULL

17802.6999

NULL

NULL

Azienda di merci sportive versatili

287

729.6344

NULL

NULL

Azienda di merci sportive versatili

288

17073.0655

NULL

DE

NULL

NULL

17802.6999

NULL

DE

NULL

287

729.6344

NULL

DE

NULL

288

17073.0655

NULL

DE

Azienda di merci sportive versatili

NULL

17802.6999

NULL

DE

Azienda di merci sportive versatili

287

729.6344

NULL

DE

Azienda di merci sportive versatili

288

17073.0655

NULL

FR

NULL

NULL

236210.9015

NULL

FR

NULL

287

27731.551

NULL

FR

NULL

290

208479.3505

NULL

FR

Fornitori di prodotti sportivi e per il benessere

NULL

236210.9015

NULL

FR

Fornitori di prodotti sportivi e per il benessere

287

27731.551

NULL

FR

Fornitori di prodotti sportivi e per il benessere

290

208479.3505

Europe

NULL

NULL

NULL

254013.6014

Europe

NULL

NULL

287

28461.1854

Europe

NULL

NULL

288

17073.0655

Europe

NULL

NULL

290

208479.3505

Europe

NULL

Fornitori di prodotti sportivi e per il benessere

NULL

236210.9015

Europe

NULL

Fornitori di prodotti sportivi e per il benessere

287

27731.551

Europe

NULL

Fornitori di prodotti sportivi e per il benessere

290

208479.3505

Europe

NULL

Azienda di merci sportive versatili

NULL

17802.6999

Europe

NULL

Azienda di merci sportive versatili

287

729.6344

Europe

NULL

Azienda di merci sportive versatili

288

17073.0655

Europe

DE

NULL

NULL

17802.6999

Europe

DE

NULL

287

729.6344

Europe

DE

NULL

288

17073.0655

Europe

DE

Azienda di merci sportive versatili

NULL

17802.6999

Europe

DE

Azienda di merci sportive versatili

287

729.6344

Europe

DE

Azienda di merci sportive versatili

288

17073.0655

Europe

FR

NULL

NULL

236210.9015

Europe

FR

NULL

287

27731.551

Europe

FR

NULL

290

208479.3505

Europe

FR

Fornitori di prodotti sportivi e per il benessere

NULL

236210.9015

Europe

FR

Fornitori di prodotti sportivi e per il benessere

287

27731.551

Europe

FR

Fornitori di prodotti sportivi e per il benessere

290

208479.3505

F. Utilizzo di CUBE con elementi composti

Nell'esempio seguente, l'operatore CUBE restituisce un set di risultati che include un raggruppamento per tutte le possibili combinazioni di colonne nell'elenco CUBE e un raggruppamento totale complessivo.

L'operatore elabora le colonne raggruppate (T.[Group], T.CountryRegionCode) e (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)) ciascuna come una sola colonna.

USE AdventureWorks2008R2;
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.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2006'
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);

Set di risultati:

Region

Country

Year

Month

Total Sales

NULL

NULL

NULL

NULL

966221.9606

NULL

NULL

2006

7

109936.0248

NULL

NULL

2006

8

296651.4808

NULL

NULL

2006

9

184477.7563

NULL

NULL

2006

10

62792.5455

NULL

NULL

2006

11

213238.0125

NULL

NULL

2006

12

99126.1407

Europe

FR

NULL

NULL

966221.9606

Europe

FR

2006

7

109936.0248

Europe

FR

2006

8

296651.4808

Europe

FR

2006

9

184477.7563

Europe

FR

2006

10

62792.5455

Europe

FR

2006

11

213238.0125

Europe

FR

2006

12

99126.1407

G. Utilizzo della clausola GROUP BY con GROUPING SETS

Nell'esempio seguente l'operatore GROUPING SETS ha quattro raggruppamenti, uno per ogni colonna nell'elenco SELECT. L'operatore restituisce una riga per ogni valore univoco nelle colonne Region, Country, Store, e SalesPersonID .

USE AdventureWorks2008R2;
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 AS S
        ON C.StoreID  = S.BusinessEntityId 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    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;

Set di risultati:

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

287

28461.1854

NULL

NULL

NULL

288

17073.0655

NULL

NULL

NULL

290

208479.3505

NULL

NULL

Fornitori di prodotti sportivi e per il benessere

NULL

236210.9015

NULL

NULL

Azienda di merci sportive versatili

NULL

17802.6999

NULL

DE

NULL

NULL

17802.6999

NULL

FR

NULL

NULL

236210.9015

Europe

NULL

NULL

NULL

254013.6014

H. Utilizzo di GROUPING SETS con elementi composti

Nell'esempio seguente l'elenco GROUPING SETS contiene due elementi composti, (T.[Group], T.CountryRegionCode) e (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)). Ogni elemento composto è considerato come una colonna.

USE AdventureWorks2008R2;
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 AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2006'
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);

Set di risultati:

Region

Country

Year

Month

Total Sales

NULL

NULL

2006

7

109936.0248

NULL

NULL

2006

8

296651.4808

NULL

NULL

2006

9

184477.7563

NULL

NULL

2006

10

62792.5455

NULL

NULL

2006

11

213238.0125

NULL

NULL

2006

12

99126.1407

Europe

FR

NULL

NULL

966221.9606

I. Utilizzo della clausola GROUP BY con più GROUPING SETS

Nell'esempio seguente l'elenco GROUPING SETS contiene cinque elementi. Il set di risultati ha una riga per i seguenti elementi:

  • Ogni combinazione univoca di valori nelle colonne Region e Country

  • Ogni valore univoco nella colonna Store.

  • Ogni combinazione univoca di valori nelle colonne SalesPersonID e Region

  • Ogni valore univoco nella colonna SalesPersonID.

  • Un totale complessivo

USE AdventureWorks2008R2;
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 AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    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;

Set di risultati:

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

NULL

254013.6014

NULL

NULL

NULL

287

28461.1854

NULL

NULL

NULL

288

17073.0655

NULL

NULL

NULL

290

208479.3505

NULL

NULL

Fornitori di prodotti sportivi e per il benessere

NULL

236210.9015

NULL

NULL

Azienda di merci sportive versatili

NULL

17802.6999

Europe

NULL

NULL

287

28461.1854

Europe

NULL

NULL

288

17073.0655

Europe

NULL

NULL

290

208479.3505

Europe

DE

NULL

NULL

17802.6999

Europe

FR

NULL

NULL

236210.9015

J. Utilizzo di GROUPING SETS con un ROLLUP di parte dell'elenco GROUP BY

Nell'esempio seguente l'elenco GROUPING SETS include raggruppamenti per le colonne T.[Group] e T.CountryRegionCode e un ROLLUP delle colonne S.Name e H.SalesPersonID.

USE AdventureWorks2008R2;
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 AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    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;

Set di risultati:

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

NULL

254013.6014

NULL

NULL

Fornitori di prodotti sportivi e per il benessere

NULL

236210.9015

NULL

NULL

Fornitori di prodotti sportivi e per il benessere

287

27731.551

NULL

NULL

Fornitori di prodotti sportivi e per il benessere

290

208479.3505

NULL

NULL

Azienda di merci sportive versatili

NULL

17802.6999

NULL

NULL

Azienda di merci sportive versatili

287

729.6344

NULL

NULL

Azienda di merci sportive versatili

288

17073.0655

NULL

DE

NULL

NULL

17802.6999

NULL

FR

NULL

NULL

236210.9015

Europe

NULL

NULL

NULL

254013.6014

K. Utilizzo di GROUPING SETS con un CUBE di parte dell'elenco GROUP BY

Nell'esempio seguente l'elenco GROUPING SETS include raggruppamenti per le colonne T.[Group] e T.CountryRegionCode e un CUBE delle colonne S.Name e H.SalesPersonID.

USE AdventureWorks2008R2;
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 AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    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;

Set di risultati:

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

NULL

254013.6014

NULL

NULL

NULL

287

28461.1854

NULL

NULL

NULL

288

17073.0655

NULL

NULL

NULL

290

208479.3505

NULL

NULL

Fornitori di prodotti sportivi e per il benessere

NULL

236210.9015

NULL

NULL

Fornitori di prodotti sportivi e per il benessere

287

27731.551

NULL

NULL

Fornitori di prodotti sportivi e per il benessere

290

208479.3505

NULL

NULL

Azienda di merci sportive versatili

NULL

17802.6999

NULL

NULL

Azienda di merci sportive versatili

287

729.6344

NULL

NULL

Azienda di merci sportive versatili

288

17073.0655

NULL

DE

NULL

NULL

17802.6999

NULL

FR

NULL

NULL

236210.9015

Europe

NULL

NULL

NULL

254013.6014