GROUP BY (Transact-SQL)

Raggruppa un set selezionato di righe in un set di righe di riepilogo in base ai valori di una o più colonne o espressioni. Per ogni gruppo viene restituita una riga. Le aggregazioni nell'<elenco di selezione> della clausola SELECT forniscono informazioni su ogni gruppo anziché su righe singole.

La clausola GROUP BY dispone di due sintassi, una conforme e una non conforme allo standard ISO. In un'unica istruzione SELECT è possibile utilizzare solo uno stile di sintassi. Utilizzare la sintassi conforme allo standard ISO per ogni nuovo lavoro. La sintassi non conforme allo standard ISO è disponibile solo per compatibilità con le versioni precedenti.

In questo argomento una clausola GROUP BY può essere descritta come generale o semplice:

• Una clausola GROUP BY generale include GROUPING SETS, CUBE, ROLLUP, WITH CUBE o WITH ROLLUP.

• Una clausola GROUP BY semplice non include GROUPING SETS, CUBE, ROLLUP, WITH CUBE o WITH ROLLUP. La clausola GROUP BY (), relativa al totale complessivo, viene considerata una clausola GROUP BY semplice.

Convenzioni della sintassi Transact-SQL (Transact-SQL)

Sintassi

ISO-Compliant Syntax

GROUP BY <group by spec>

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

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

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

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

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

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

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

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

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

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

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

<empty group> ::=
        ( )

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

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

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

Argomenti

• <column_expression>

  • Espressione in base alla quale viene eseguita l'operazione di raggruppamento.

• ROLLUP ( )
  Genera righe di aggregazione di GROUP BY semplice, righe del subtotale o di sovraggregazione, nonché una riga del totale complessivo.

  Il numero di raggruppamenti restituiti è uguale al numero di espressioni presenti nell'<elenco di elementi composti> più uno. Si consideri ad esempio l'istruzione seguente:

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

  Viene generata una riga con un subtotale per ogni combinazione univoca di valori di (a, b, c), (a, b) e (a), e viene calcolata anche una riga del totale complessivo.

  Il rollup delle colonne viene eseguito da destra a sinistra. L'ordine delle colonne influisce sui raggruppamenti di output di ROLLUP e può influire sul numero di righe nel set di risultati.

• CUBE ( )
  Genera righe di aggregazione di GROUP BY semplice, righe di sovraggregazione di ROLLUP e righe a tabulazione incrociata.

  CUBE restituisce un raggruppamento per tutte le permutazioni di espressioni presenti nell'<elenco di elementi composti>.

  Il numero di raggruppamenti generati è uguale a (2n), dove n rappresenta il numero di espressioni presenti nell'<elenco di elementi composti>. Si consideri ad esempio l'istruzione seguente:

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

  Viene generata una riga per ogni combinazione univoca di valori di (a, b, c), (a, b), (a, c), (b, c), (a), (b) e (c) con un subtotale per ogni riga e una riga del totale complessivo.

  L'ordine delle colonne non influisce sull'output di CUBE.

• GROUPING SETS ( )
  Specifica più raggruppamenti di dati in una query. Vengono aggregati solo i gruppi specificati e non il set completo di aggregazioni generate da CUBE o da ROLLUP. I risultati rappresentano l'equivalente di quelli della clausola UNION ALL dei gruppi specificati. GROUPING SETS può contenere un solo elemento o un elenco di elementi e può specificare raggruppamenti equivalenti a quelli restituiti da ROLLUP o CUBE. Per alcuni esempi, vedere Equivalenti GROUPING SETS. L'<elenco di elementi del set di raggruppamenti> può contenere ROLLUP o CUBE.

• ()
  Il gruppo vuoto genera un totale.

Sintassi non conforme allo standard ISO

• ALL
  Questa caratteristica verrà rimossa a partire da una delle prossime versioni di Microsoft SQL Server. Evitare di utilizzare questa funzionalità in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata. Include tutti i gruppi e i set di risultati, compresi quelli in cui nessuna riga soddisfa la condizione di ricerca specificata nella clausola WHERE. Quando viene specificata l'opzione ALL, per le colonne di riepilogo dei gruppi che non soddisfano la condizione di ricerca vengono restituiti valori Null. Non è possibile specificare l'opzione ALL con gli operatori CUBE o ROLLUP.

  Le operazioni GROUP BY ALL non sono supportate in query che accedono a tabelle remote se la query include anche una clausola WHERE. Questo tipo di operazione avrà esito negativo su colonne che presentano l'attributo FILESTREAM.

• group_by_expression
  Espressione in base alla quale viene eseguito un raggruppamento. L'argomento group_by_expression è noto anche come colonna di raggruppamento. group_by expression può essere una colonna oppure un'espressione non di aggregazione che fa riferimento a una colonna restituita dalla clausola FROM. Non è possibile utilizzare un alias di colonna definito nell'elenco SELECT per specificare una colonna di raggruppamento.

  [!NOTA]

  Le colonne di tipo text, ntext e image non possono essere utilizzate in group_by_expression.

  Per clausole GROUP BY che non includono gli operatori CUBE o ROLLUP, il numero di elementi group_by_expression è limitato dalle dimensioni delle colonne GROUP BY, dalle colonne di aggregazione e dai valori di aggregazione interessati dalla query. Tale limite è correlato al limite di 8.060 byte della tabella di lavoro intermedia necessaria per mantenere i risultati intermedi delle query. Quando viene specificato l'operatore CUBE o ROLLUP, sono consentite al massimo 12 espressioni di raggruppamento.

  I metodi con tipo di dati xml non possono essere specificati direttamente in group_by_expression. In alternativa, è possibile fare riferimento a una funzione definita dall'utente che utilizza i metodi con tipo di dati xml al suo interno oppure a una colonna calcolata che utilizza tali metodi.

• WITH CUBE
  Questa caratteristica verrà rimossa a partire da una delle prossime versioni di Microsoft SQL Server. Evitare di utilizzare questa funzionalità in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata. Specifica che, oltre alle righe normalmente restituite dalla clausola GROUP BY, nel set di risultati vengono inserite righe di riepilogo. Viene restituita una riga di riepilogo GROUP BY per ogni possibile combinazione di gruppo e sottogruppo nel set di risultati. Utilizzare la funzione GROUPING per determinare se i valori Null del set di risultati sono valori di riepilogo GROUP BY.

  Il numero di righe di riepilogo del set di risultati è determinato dal numero di colonne elencate nella clausola GROUP BY. Poiché l'operatore CUBE restituisce ogni possibile combinazione di gruppo e sottogruppo, il numero di righe è lo stesso, indipendentemente dall'ordine in cui vengono specificate le colonne di raggruppamento.

• WITH ROLLUP
  Questa caratteristica verrà rimossa a partire da una delle prossime versioni di Microsoft SQL Server. Evitare di utilizzare questa funzionalità in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata. Specifica che, oltre alle righe normalmente restituite dalla clausola GROUP BY, nel set di risultati vengono inserite righe di riepilogo. I gruppi vengono riepilogati in ordine gerarchico, dal livello inferiore al livello superiore del gruppo. La gerarchia del gruppo viene determinata dall'ordine in cui vengono specificate le colonne di raggruppamento. La modifica dell'ordine delle colonne di raggruppamento può influire sul numero di righe restituite nel set di risultati.

  Importante
  Le aggregazioni Distinct, ad esempio AVG (DISTINCT column_name), COUNT (DISTINCT column_name) e SUM (DISTINCT column_name), non sono supportate da CUBE o ROLLUP. Se si utilizzano tali operatori, il Motore di database di SQL Server restituisce un messaggio di errore e la query viene annullata.

Osservazioni

Le espressioni della clausola GROUP BY possono contenere colonne delle tabelle, tabelle derivate o viste della clausola FROM. Non è necessario che nell'<elenco di selezione> della clausola SELECT vengano visualizzate colonne.

Ogni colonna della tabella o della vista di qualsiasi espressione non di aggregazione presente nell'<elenco di selezione> deve essere inclusa nell'elenco GROUP BY:

• Le istruzioni seguenti sono consentite:

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

• Le istruzioni seguenti non sono consentite:

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

Se l'<elenco di selezione> della clausola SELECT include aggregazioni, la clausola GROUP BY calcola un valore di riepilogo per ogni gruppo. Tali funzioni sono denominate aggregazioni vettoriali.

Le righe che non soddisfano le condizioni presenti nella clausola WHERE vengono rimosse prima che venga eseguita qualsiasi operazione di raggruppamento.

La clausola HAVING viene utilizzata con la clausola GROUP BY per filtrare gruppi nel set di risultati.

La clausola GROUP BY non ordina il set di risultati. Per eseguire questa operazione, utilizzare la clausola ORDER BY.

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

Non è possibile utilizzare GROUP BY con un alias per sostituire un nome di colonna nella clausola AS, a meno che l'alias non sostituisca un nome di colonna di una tabella derivata nella clausola FROM.

Eventuali set di raggruppamenti duplicati in un elenco GROUPING SETS non vengono eliminati. I set di raggruppamenti duplicati possono essere generati specificando più volte un'espressione di colonna oppure elencando un'espressione di colonna generata anche da un operatore CUBE o ROLLUP nell'elenco GROUPING SETS.

Le aggregazioni Distinct, ad esempio AVG (DISTINCT column_name), COUNT (DISTINCT column_name) e SUM (DISTINCT column_name), sono supportate da ROLLUP, CUBE e GROUPING SETS.

ROLLUP, CUBE e GROUPING SETS non possono essere specificati in una vista indicizzata.

La clausola GROUP BY o HAVING non può essere utilizzata direttamente su colonne con tipo di dati ntext, text o image. Queste colonne possono essere utilizzate come argomenti in funzioni che restituiscono un valore di un altro tipo di dati, ad esempio SUBSTRING () e CAST ().

I metodi con tipo di dati xml non possono essere specificati direttamente in <column_expression>. In alternativa, è possibile fare riferimento a una funzione definita dall'utente che utilizza i metodi con tipo di dati xml al suo interno oppure a una colonna calcolata che utilizza tali metodi.

Limitazioni della clausola GROUP BY per GROUPING SETS, ROLLUP e CUBE

Limitazioni della sintassi

Nella clausola GROUP BY non è consentito utilizzare GROUPING SETS a meno che non appartengano a un elenco GROUPING SETS. Non è consentito ad esempio utilizzare GROUP BY C1, (C2,..., Cn), mentre è consentito utilizzare GROUP BY GROUPING SETS (C1, (C2, ..., Cn)).

L'utilizzo di GROUPING SETS non è consentito all'interno di GROUPING SETS. L'utilizzo di GROUP BY GROUPING SETS (C1, GROUPING SETS (C2, C3)), ad esempio, non è consentito.

Le parole chiave ALL, WITH CUBE e WITH ROLLUP non conformi allo standard ISO non sono consentite in una clausola GROUP BY con la parola chiave ROLLUP, CUBE o GROUPING SETS.

Limitazioni delle dimensioni

Per la clausola GROUP BY semplice non è previsto alcun limite per il numero di espressioni.

Per una clausola GROUP BY che utilizza ROLLUP, CUBE o GROUPING SETS, il numero massimo di espressioni è 32 e il numero massimo di set di raggruppamenti che possono essere generati è 4.096 (212). Gli esempi seguenti non possono essere eseguiti correttamente perché la clausola GROUP BY è troppo complessa:

• Nell'esempio seguente vengono generati 8.192 (213) set di raggruppamenti.

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

• Nell'esempio seguente vengono generati 4.097 (212 + 1) set di raggruppamenti.

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

• Anche nell'esempio seguente vengono generati 4.097 (212 + 1) set di raggruppamenti. Sia CUBE () che il set di raggruppamenti () generano una riga del totale complessivo e i set di raggruppamenti duplicati non vengono eliminati.

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

Supporto per le funzionalità GROUP BY di SQL-2006 ISO e ANSI

SQL Server 2008 supporta tutte le funzionalità GROUP BY incluse nello standard SQL-2006 con le eccezioni di sintassi seguenti:

• Nella clausola GROUP BY non è consentito utilizzare set di raggruppamenti a meno che non appartengano a un elenco GROUPING SETS esplicito. È ad esempio consentito utilizzare GROUP BY Column1, (Column2, ...ColumnN) nello standard, ma non in SQL Server. È consentito l'utilizzo di GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)) o GROUP BY Column1, Column2, ... ColumnN. Queste espressioni sono semanticamente equivalenti a quelle dell'esempio relativo a GROUP BY precedente. Ciò consente di evitare che GROUP BY Column1, (Column2, ...ColumnN) possa essere erroneamente interpretato come GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)), che non è semanticamente equivalente.

• L'utilizzo di set di raggruppamenti non è consentito all'interno di GROUPING SETS. È consentito utilizzare ad esempio GROUP BY GROUPING SETS (A1, A2,…An, GROUPING SETS (C1, C2, ...Cn)) nello standard SQL-2006, ma non in SQL Server. In SQL Server 2008 è possibile utilizzare GROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn ) o GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) ). Questi esempi sono semanticamente equivalenti al primo esempio relativo a GROUP BY, ma sono caratterizzati da una sintassi più chiara.

• L'utilizzo di GROUP BY [ALL/DISTINCT] non è consentito in una clausola GROUP BY generale o con il costrutto GROUPING SETS, ROLLUP, CUBE, WITH CUBE o WITH ROLLUP. ALL è il valore predefinito ed è implicito.

Confronto tra le funzionalità GROUP BY supportate

Nella tabella seguente vengono descritte le funzionalità GROUP BY supportate in base alla versione di SQL Server e al livello di compatibilità del database.

Funzionalità	SQL Server 2005 Integration Services	Livello di compatibilità di SQL Server 2008 pari a 100	Livello di compatibilità di SQL Server 2008 inferiore o pari a 90
Aggregazioni DISTINCT	Non supportate per WITH CUBE o WITH ROLLUP.	Supportate per WITH CUBE, WITH ROLLUP, GROUPING SETS, CUBE o ROLLUP.	Comportamento analogo al livello di compatibilità di SQL Server 2008 pari a 100.
Funzione definita dall'utente denominata CUBE o ROLLUP nella clausola GROUP BY	L'utilizzo della funzione definita dall'utente dbo.cube(arg1,...argN) o dbo.rollup(arg1,...argN) nella clausola GROUP BY è consentito. Esempio: SELECT SUM (x) FROM T GROUP BY dbo.cube(y)	L'utilizzo della funzione definita dall'utente dbo.cube (arg1,...argN) o dbo.rollup(arg1,...argN) nella clausola GROUP BY non è consentito. Esempio: SELECT SUM (x) FROM T GROUP BY dbo.cube(y) Viene restituito il messaggio di errore seguente: "Sintassi non corretta in prossimità della parola chiave 'cube'|'rollup'". Per evitare questo problema, sostituire dbo.cube con [dbo].[cube] oppure dbo.rollup con [dbo].[rollup]. L'esempio seguente è consentito: SELECT SUM (x) FROM T GROUP BY [dbo].[cube](y)	L'utilizzo della funzione definita dall'utente dbo.cube (arg1,...argN) o dbo.rollup(arg1,...argN) nella clausola GROUP BY è consentito. Esempio: SELECT SUM (x) FROM T GROUP BY dbo.cube(y)
GROUPING SETS	Non supportato	Supportato	Supportato
CUBE	Non supportato	Supportato	Non supportato
ROLLUP	Non supportato	Supportato	Non supportato
Totale complessivo, ad esempio GROUP BY ()	Non supportato	Supportato	Supportato
Funzione GROUPING_ID	Non supportata	Supportata	Supportata
Funzione GROUPING	Supportata	Supportata	Supportata
WITH CUBE	Supportato	Supportato	Supportato
WITH ROLLUP	Supportato	Supportato	Supportato
Rimozione di raggruppamenti duplicati relativa a WITH CUBE o WITH ROLLUP	Supportata	Supportata	Supportata

Esempi

Per esempi che utilizzano GROUPING SETS, ROLLUP e CUBE, vedere Utilizzo della clausola GROUP BY con gli operatori ROLLUP, CUBE e GROUPING SETS.

A. Utilizzo di una clausola GROUP BY semplice

Nell'esempio seguente viene recuperato il totale per ogni SalesOrderID dalla tabella SalesOrderDetail.

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

B. Utilizzo di una clausola GROUP BY con più tabelle

Nell'esempio seguente viene recuperato il numero di dipendenti per ogni City dalla tabella Address unita in join alla tabella EmployeeAddress.

USE AdventureWorks;
GO
SELECT a.City, COUNT(ea.AddressID) EmployeeCount
FROM HumanResources.EmployeeAddress ea 
    INNER JOIN Person.Address a
        ON ea.AddressID = a.AddressID
GROUP BY a.City
ORDER BY a.City;

C. Utilizzo di una clausola GROUP BY con un'espressione

Nell'esempio seguente vengono recuperate le vendite totali per ogni anno tramite la funzione DATEPART. La stessa espressione deve essere presente sia nell'elenco SELECT che nella clausola GROUP BY.

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

D. Utilizzo di una clausola GROUP BY con una clausola HAVING

Nell'esempio seguente viene utilizzata la clausola HAVING per specificare quale gruppo generato nella clausola GROUP BY deve essere incluso nel set di risultati.

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

Vedere anche

Riferimento

GROUPING_ID (Transact-SQL)

GROUPING (Transact-SQL)

SELECT (Transact-SQL)

SELECT Clause (Transact-SQL)

Concetti

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

Equivalenti GROUPING SETS

Risoluzione dei problemi relativi agli errori in GROUP BY