SELECT - GROUP BY- Transact-SQL

  • Articolo

Si applica a:SQL Server database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics AnalyticsPlatform System (PDW)SQL analytics endpoint in Microsoft FabricWarehouse in Microsoft Fabric

Clausola di istruzione edizione Standard LECT che divide il risultato della query in gruppi di righe, in genere eseguendo una o più aggregazioni in ogni gruppo. L'istruzione SELECT restituisce una riga per ogni gruppo.

Sintassi

Convenzioni di sintassi Transact-SQL

-- Syntax for SQL Server and Azure SQL Database   
-- ISO-Compliant Syntax  
  
GROUP BY {
      column-expression  
    | ROLLUP ( <group_by_expression> [ ,...n ] )  
    | CUBE ( <group_by_expression> [ ,...n ] )  
    | GROUPING SETS ( <grouping_set> [ ,...n ]  )  
    | () --calculates the grand total 
} [ ,...n ] 
 
<group_by_expression> ::=  
      column-expression  
    | ( column-expression [ ,...n ] )    
   
<grouping_set> ::=  
      () --calculates the grand total  
    | <grouping_set_item>  
    | ( <grouping_set_item> [ ,...n ] )  
  
<grouping_set_item> ::=  
      <group_by_expression>  
    | ROLLUP ( <group_by_expression> [ ,...n ] )  
    | CUBE ( <group_by_expression> [ ,...n ] )  
  

-- For backward compatibility only.
-- Non-ISO-Compliant Syntax for SQL Server and Azure SQL Database 
  
GROUP BY {
       ALL column-expression [ ,...n ] 
    | column-expression [ ,...n ]  WITH { CUBE | ROLLUP }    
       }


-- Syntax for Azure Synapse Analytics 
  
GROUP BY {
      column-name [ WITH (DISTRIBUTED_AGG) ]  
    | column-expression
    | ROLLUP ( <group_by_expression> [ ,...n ] ) 
} [ ,...n ]


-- Syntax for Parallel Data Warehouse  
  
GROUP BY {
      column-name [ WITH (DISTRIBUTED_AGG) ]  
    | column-expression
} [ ,...n ]

Nota

Per visualizzare la sintassi Transact-SQL per SQL Server 2014 (12.x) e versioni precedenti, vedere la documentazione delle versioni precedenti.

Argomenti

column-expression

Specifica una colonna o un calcolo di non aggregazione in una colonna. Questa colonna può appartenere a una tabella, una tabella derivata o una visualizzazione. La colonna deve essere presente nella clausola FROM dell'istruzione SELECT, ma non è necessario che sia inclusa nell'elenco SELECT.

Per le espressioni valide, vedere Espressioni.

La colonna deve essere presente nella clausola FROM dell'istruzione SELECT, ma non è necessario che sia inclusa nell'elenco SELECT. Tuttavia, ogni colonna della tabella o della visualizzazione di qualsiasi espressione di non aggregazione presente nell'elenco <select> 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;

L'espressione della colonna non può contenere:

  • L'alias di una colonna definita nell'elenco SELECT. Può usare l'alias di una colonna per una tabella derivata definita nella clausola FROM.
  • Una colonna di tipo text, ntext o image. È possibile tuttavia usare una colonna text, ntext o image come argomento di una funzione che restituisce un valore di un tipo di dati valido. Ad esempio, l'espressione può usare SUBSTRING() e CAST(). Ciò si applica anche alle espressioni nella clausola HAVING.
  • metodi con tipo di dati xml. Può includere una funzione definita dall'utente che usa metodi con tipo di dati xml. Può includere una colonna calcolata che usa metodi con tipo di dati xml.
  • Sottoquery. Viene restituito l'errore 144.
  • Una colonna di una visualizzazione indicizzata.

GROUP BY column-expression [ ,...n ]

Raggruppa i risultati dell'istruzione SELECT in base ai valori in un elenco di una o più espressioni di colonna.

Ad esempio, questa query crea una tabella Sales con colonne per Country, Region e Sales. Inserisce quattro righe, due delle quali con valori corrispondenti per Country e Region.

CREATE TABLE Sales ( Country VARCHAR(50), Region VARCHAR(50), Sales INT );

INSERT INTO sales VALUES (N'Canada', N'Alberta', 100);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 200);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 300);
INSERT INTO sales VALUES (N'United States', N'Montana', 100);

La tabella Sales contiene le righe seguenti:

Paese Region Vendite
Canada Alberta 100
Canada British Columbia 200
Canada British Columbia 300
Stati Uniti Montana 100

La query successiva raggruppa Country e Region e restituisce la somma aggregata per ogni combinazione di valori.

SELECT Country, Region, SUM(sales) AS TotalSales
FROM Sales
GROUP BY Country, Region;

Il risultato della query ha 3 righe perché sono presenti 3 combinazioni di valori per Country e Region. Il valore TotalSales per Canada e British Columbia è la somma di due righe.

Paese Region TotalSales
Canada Alberta 100
Canada British Columbia 500
Stati Uniti Montana 100

GROUP BY ROLLUP

Crea un gruppo per ogni combinazione di espressioni di colonna. Esegue anche il rollup dei risultati in subtotali e totali complessivi. Per eseguire questa operazione, esegue uno spostamento da destra a sinistra riducendo il numero di espressioni di colonna su cui crea gruppi e aggregazioni.

L'ordine delle colonne ha effetto sull'output di ROLLUP e può avere effetto sul numero di righe nel set di risultati.

Ad esempio, GROUP BY ROLLUP (col1, col2, col3, col4) crea gruppi per ogni combinazione di espressioni di colonna negli elenchi seguenti.

  • col1, col2, col3, col4
  • col1, col2, col3, NULL
  • col1, col2, NULL, NULL
  • col1, NULL, NULL, NULL
  • NULL, NULL, NULL, NULL --Totale complessivo

Usando la tabella dell'esempio precedente, questo codice esegue un'operazione GROUP BY ROLLUP anziché una semplice operazione GROUP BY.

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP (Country, Region);

Il risultato della query ha le stesse aggregazioni di GROUP BY senza ROLLUP. Inoltre, crea i subtotali per ogni valore di Country. Infine, visualizza un totale complessivo per tutte le righe. Il risultato è simile al seguente:

Paese Region TotalSales
Canada Alberta 100
Canada British Columbia 500
Canada NULL 600
Stati Uniti Montana 100
Stati Uniti NULL 100
NULL NULL 700

GROUP BY CUBE ( )

GROUP BY CUBE crea gruppi per tutte le possibili combinazioni di colonne. Per GROUP BY CUBE (a, b) il risultato include gruppi per i valori univoci di (a, b), (NULL, b), (a, NULL) e (NULL, NULL).

Usando la tabella degli esempi precedenti, questo codice esegue un'operazione GROUP BY CUBE in Country e Region.

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE (Country, Region);

Il risultato della query include gruppi per i valori univoci di (Country, Region), (NULL, Region), (Country, NULL) e (NULL, NULL). I risultati sono simili ai seguenti:

Paese Region TotalSales
Canada Alberta 100
NULL Alberta 100
Canada British Columbia 500
NULL British Columbia 500
Stati Uniti Montana 100
NULL Montana 100
NULL NULL 700
Canada NULL 600
Stati Uniti NULL 100

GROUP BY GROUPING SETS ( )

L'opzione GROUPING SETS consente di combinare più clausole GROUP BY in un'unica clausola GROUP BY. I risultati rappresentano l'equivalente di quelli della clausola UNION ALL dei gruppi specificati.

Ad esempio, GROUP BY ROLLUP (Country, Region) e GROUP BY GROUPING SETS ( ROLLUP (Country, Region) ) restituiscono gli stessi risultati.

Quando GROUPING SETS ha due o più elementi, i risultati sono un'unione degli elementi. Questo esempio restituisce l'unione dei risultati di ROLLUP e CUBE per Country e Region.

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS ( ROLLUP (Country, Region), CUBE (Country, Region) );

I risultati sono gli stessi di questa query che restituisce un'unione delle due istruzioni GROUP BY.

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP (Country, Region)
UNION ALL
SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE (Country, Region);

SQL non consolida i gruppi duplicati generati per un elenco GROUPING SETS. Ad esempio, in GROUP BY ( (), CUBE (Country, Region) ) entrambi gli elementi restituiscono una riga per il totale complessivo ed entrambe le righe saranno elencate nei risultati.

GROUP BY ()

Specifica il gruppo vuoto, che genera il totale complessivo. Ha la stessa funzione di uno degli elementi di GROUPING SET. Ad esempio, questa istruzione fornisce le vendite totali per ogni paese/area geografica e quindi assegna il totale complessivo per tutti i paesi/aree geografiche.

SELECT Country, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS ( Country, () );

GROUP BY ALL column-expression [ ,...n ]

Si applica a: SQL Server e database SQL di Azure

Nota

questa sintassi è disponibile solo per la compatibilità con le versioni precedenti. Verrà rimossa in una versione futura. Evitare pertanto di usarla in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni che attualmente usano questa sintassi.

Specifica di includere tutti i gruppi nei risultati indipendentemente dal fatto che soddisfino i criteri di ricerca nella clausola WHERE. I gruppi che non soddisfano i criteri di ricerca hanno valore NULL per l'aggregazione.

GROUP BY ALL:

  • Questa operazione non è supportata in query che accedono a tabelle remote se la query include anche una clausola WHERE.
  • L'operazione avrà esito negativo nelle colonne con l'attributo FILESTREAM.

GROUP BY column-expression [ ,...n ] WITH { CUBE | ROLLUP }

Si applica a: SQL Server e database SQL di Azure

Nota

questa sintassi è disponibile solo per la compatibilità con le versioni precedenti. Evitare pertanto di usarla in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni che attualmente usano questa sintassi.

WITH (DISTRIBUTED_AGG)

Si applica a: Azure Synapse Analytics, Piattaforma di strumenti analitici (PDW)

L'hint per la query DISTRIBUTED_AGG forza nel sistema di elaborazione parallela elevata (Massively Parallel Processing, MPP) la ridistribuzione di una tabella in una colonna specifica prima dell'esecuzione di un'aggregazione. Solo una colonna della clausola GROUP BY può avere un hint per la query DISTRIBUTED_AGG. Al termine dell'esecuzione della query, la tabella ridistribuita viene eliminata. La tabella originale non viene modificata.

NOTA: l'hint per la query DISTRIBUTED_AGG è incluso per la compatibilità con le versioni precedenti della piattaforma di strumenti analitici e non migliora le prestazioni per la maggior parte delle query. Per impostazione predefinita, MPP distribuisce già i dati in base alle esigenze per migliorare le prestazioni per le aggregazioni.

Osservazioni generali

Interazione di GROUP BY con l'istruzione SELECT

Elenco SELECT:

  • Aggregazione di vettori. Se l'elenco SELECT include funzioni di aggregazione, la clausola GROUP BY calcola un valore di riepilogo per ogni gruppo. Tali funzioni sono denominate aggregazioni vettoriali.
  • Aggregazioni DISTINCT. Le aggregazioni AVG (DISTINCT column_name), COUNT (DISTINCT column_name) e SUM (DISTINCT column_name) sono supportate con ROLLUP, CUBE e GROUPING SETS.

Clausola WHERE:

  • SQL rimuove le righe che non soddisfano le condizioni presenti nella clausola WHERE prima che venga eseguita qualsiasi operazione di raggruppamento.

Clausola HAVING:

  • SQL usa la clausola HAVING per filtrare i gruppi nel set di risultati.

Clausola ORDER BY:

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

Valori NULL:

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

Limitazioni e restrizioni

Si applica a: SQL Server (a partire dalla versione 2008) e Azure Synapse Analytics

Capacità massima

Per una clausola GROUP BY che usa ROLLUP, CUBE o GROUPING SETS, il numero massimo di espressioni è 32. Il numero massimo di gruppi è 4096 (212). Gli esempi seguenti non possono essere eseguiti correttamente perché la clausola GROUP BY ha più di 4096 gruppi.

  • L'esempio seguente genera 4097 (212 + 1) set di raggruppamenti e ha esito negativo.

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

  • L'esempio seguente genera 4097 (212 + 1) gruppi e ha esito negativo. Sia CUBE () che il () set di raggruppamento producono una riga totale completa e i set di raggruppamento duplicati non vengono eliminati.

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

  • Questo esempio usa la sintassi compatibile con le versioni precedenti. Genera 8192 (213) set di raggruppamenti e ha esito negativo.

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

    Per le clausole GROUP BY compatibili con le versioni precedenti che non contengono CUBE o ROLLUP, il numero di elementi raggruppati per elementi è limitato dalle dimensioni delle colonne GROUP BY, dalle colonne aggregate e dai valori aggregati coinvolti nella 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.

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

La clausola GROPU BY supporta tutte le funzionalità GROUP BY incluse nello standard SQL-2006 con le eccezioni di sintassi seguenti:

  • I set di raggruppamento non sono consentiti nella clausola GROUP BY, a meno che non facciano parte di un elenco grouping edizione Standard TS esplicito. Ad esempio, GROUP BY Column1, (Column2, ...ColumnN) è consentito nello standard ma non in Transact-SQL. Transact-SQL supporta GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)) e GROUP BY Column1, Column2, ... ColumnN che sono semanticamente equivalenti. Queste espressioni sono semanticamente equivalenti a quelle dell'esempio relativo a GROUP BY precedente. Ciò consente di evitare la possibilità che GROUP BY Column1, (Column2, ...ColumnN) venga interpretato erroneamente come GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)), che non sono semanticamente equivalenti.

  • I set di raggruppamento non sono consentiti all'interno di set di raggruppamento. Ad esempio, GROUP BY GROUPING SETS (A1, A2,...An, GROUPING SETS (C1, C2, ...Cn)) è consentito nello standard SQL-2006 ma non in Transact-SQL. GROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn ) o GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) ) sono consentiti in Transact-SQL e sono semanticamente equivalenti al primo esempio di GROUP BY ma hanno una sintassi più chiara.

  • GROUP BY [ALL/DISTINCT] è consentito solo in una clausola GROUP BY semplice che contiene espressioni di colonna. Non è consentito con i costrutti GROUPING edizione Standard TS, ROLLUP, CUBE, WITH CUBE o WITH ROLLUP. ALL è il valore predefinito ed è implicito. È anche consentito solo nella sintassi compatibile con le versioni precedenti.

Confronto tra le funzionalità GROUP BY supportate

La tabella seguente descrive le funzionalità GROUP BY supportate in base alle versioni SQL e al livello di compatibilità del database.

Funzionalità SQL Server Integration Services Livello di compatibilità di SQL Server 100 o superiore Livello di compatibilità di SQL Server 2008 o versioni successive 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à pari a 100.
Funzione definita dall'utente denominata CUBE o ROLLUP nella clausola GROUP BY La funzione definita dall'utente dbo.cube(arg1,...argN) o dbo.rollup(arg1,...argN) nella clausola GROUP BY è consentita.

ad esempio SELECT SUM (x) FROM T GROUP BY dbo.cube(y);		 La funzione definita dall'utente dbo.cube (arg1,...argN**)** o dbo.rollup(arg1,...argN) nella clausola GROUP BY non è consentita.

ad esempio SELECT SUM (x) FROM T GROUP BY dbo.cube(y);

Viene restituito il messaggio di errore seguente: "Sintassi non corretta vicino alla 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);		 La funzione definita dall'utente dbo.cube (arg1,...argN) o dbo.rollup(arg1,...argN) nella clausola GROUP BY è consentita

ad esempio SELECT SUM (x) FROM T GROUP BY dbo.cube(y);
GROUPING SETS Non supportato Supportata Supportata
CUBE Non supportato Supportato Non supportato
ROLLUP Non supportato Supportato Non supportato
Totale complessivo, ad esempio GROUP BY () Non supportato Supportata Supportata
GROUPING_ID - funzione Non supportato Supportata Supportata
GROUPING - funzione Supportata Supportato Supportata
WITH CUBE Supportata Supportato Supportata
WITH ROLLUP Supportata Supportato Supportata
Rimozione di raggruppamenti duplicati relativa a WITH CUBE o WITH ROLLUP Supportata Supportato Supportata

Esempi

R. Uso di una clausola GROUP BY semplice

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

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

B. Uso 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. Questo esempio usa AdventureWorks.

SELECT a.City, COUNT(bea.AddressID) EmployeeCount  
FROM Person.BusinessEntityAddress AS bea   
    INNER JOIN Person.Address AS a  
        ON bea.AddressID = a.AddressID  
GROUP BY a.City  
ORDER BY a.City;

C. Uso 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.

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. Uso 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.

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

Esempi: Azure Synapse Analytics e Parallel Data Warehouse

E. Uso di base della clausola GROUP BY

L'esempio seguente trova l'importo totale di tutte le vendite in ogni giornata. Viene restituita una sola riga contenente il totale di tutte le vendite per ogni giornata.

-- Uses AdventureWorksDW  
  
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales  
GROUP BY OrderDateKey ORDER BY OrderDateKey;

F. Uso di base dell'hint DISTRIBUTED_AGG

Questo esempio usa l'hint per la query DISTRIBUTED_AGG per forzare nell'appliance la distribuzione casuale della tabella nella colonna CustomerKey prima di eseguire l'aggregazione.

-- Uses AdventureWorksDW  
  
SELECT CustomerKey, SUM(SalesAmount) AS sas  
FROM FactInternetSales  
GROUP BY CustomerKey WITH (DISTRIBUTED_AGG)  
ORDER BY CustomerKey DESC;

G. Variazioni della sintassi per GROUP BY

Quando l'elenco SELECT non include aggregazioni, ogni colonna dell'elenco SELECT deve essere inclusa nell'elenco GROUP BY. Le colonne calcolate dell'elenco SELECT possono essere incluse, ma non sono richieste, nell'elenco GROUP BY. Di seguito sono riportati esempi di istruzioni SELECT sintatticamente valide:

-- Uses AdventureWorks  
  
SELECT LastName, FirstName FROM DimCustomer GROUP BY LastName, FirstName;  
SELECT NumberCarsOwned FROM DimCustomer GROUP BY YearlyIncome, NumberCarsOwned;  
SELECT (SalesAmount + TaxAmt + Freight) AS TotalCost FROM FactInternetSales GROUP BY SalesAmount, TaxAmt, Freight;  
SELECT SalesAmount, SalesAmount*1.10 SalesTax FROM FactInternetSales GROUP BY SalesAmount;  
SELECT SalesAmount FROM FactInternetSales GROUP BY SalesAmount, SalesAmount*1.10;

H. Uso di GROUP BY con più espressioni GROUP BY

L'esempio seguente raggruppa i risultati usando più criteri GROUP BY. Se, all'interno di ogni gruppo OrderDateKey, sono presenti sottogruppi che possono essere differenziati da DueDateKey, verrà definito un nuovo raggruppamento per il set di risultati.

-- Uses AdventureWorks  
  
SELECT OrderDateKey, DueDateKey, SUM(SalesAmount) AS TotalSales   
FROM FactInternetSales
GROUP BY OrderDateKey, DueDateKey   
ORDER BY OrderDateKey;

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

L'esempio seguente usa la clausola HAVING per specificare i gruppi generati nella clausola GROUP BY che devono essere inclusi nel set di risultati. Nei risultati verranno inclusi solo i gruppi con date dell'ordine del 2004 o successive.

-- Uses AdventureWorks  
  
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales   
FROM FactInternetSales  
GROUP BY OrderDateKey   
HAVING OrderDateKey > 20040000   
ORDER BY OrderDateKey;

Vedi anche

GROUPING_ID (Transact-SQL)
GROUPING (Transact-SQL)
SELECT (Transact-SQL)
Clausola edizione Standard LECT (Transact-SQL)