Riepilogare i dati con GROUP BY
Sebbene le funzioni di aggregazione siano utili per l'analisi, è possibile suddividere i dati in subset prima di riepilogarli. In questa sezione si apprenderà come eseguire questa operazione usando la clausola GROUP BY.
Uso della clausola GROUP BY
Come è già stato descritto, quando l'istruzione SELECT viene elaborata, dopo la valutazione della clausola FROM e della clausola WHERE viene creata una tabella virtuale. Il contenuto della tabella virtuale è ora disponibile per un'ulteriore elaborazione. È possibile usare la clausola GROUP BY per suddividere il contenuto di questa tabella virtuale in gruppi di righe.
Per raggruppare le righe, specificare uno o più elementi nella clausola GROUP BY:
GROUP BY <value1> [, <value2>, …]
GROUP BY crea gruppi e inserisce righe in ogni gruppo in base agli elementi specificati nella clausola.
Ad esempio, la query seguente restituisce un set di righe raggruppate, una riga per CustomerID nella tabella Sales.SalesOrderHeader. Esaminando il processo GROUP BY è possibile osservare anche che tutte le righe con lo stesso valore per CustomerID verranno raggruppate e restituite in una singola riga dei risultati.
SELECT CustomerID
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
La query precedente equivale alla query seguente:
SELECT DISTINCT CustomerID
FROM Sales.SalesOrderHeader
Dopo che la clausola GROUP BY è stata elaborata e che ogni riga è stata associata a un gruppo, le fasi successive della query devono aggregare tutti gli elementi delle righe di origine presenti nell'elenco SELECT ma che non sono visualizzati nell'elenco GROUP BY. Questo requisito avrà un impatto sul modo in cui si scrivono le clausole SELECT e HAVING.
Qual è quindi la differenza tra scrivere la query con GROUP BY e scriverla con DISTINCT? Se si vogliono solo individuare i valori distinti per CustomerID, non esiste alcuna differenza. Tuttavia, con GROUP BY è possibile aggiungere altri elementi all'elenco SELECT che vengono quindi aggregati per ogni gruppo.
La funzione di aggregazione più semplice è COUNT(*). La query seguente accetta le 830 righe di origine originali da CustomerID e le raggruppa in 89 gruppi, in base ai valori CustomerID. Ogni valore CustomerID distinto genera una riga di output nella query GROUP BY
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
Poiché la query aggrega e conta le righe per ogni valore CustomerID, il risultato mostra il numero di righe della tabella SalesOrderHeader che appartengono a ogni cliente.
CustomerID
OrderCount
1234
3
1005
1
Si noti che GROUP BY non garantisce l'ordine dei risultati. Spesso, a causa del modo in cui Query Processor esegue l'operazione di raggruppamento, i risultati vengono restituiti nell'ordine dei valori del gruppo. Tuttavia, non è consigliabile fare affidamento su questo comportamento. Se è necessario ordinare i risultati, includere in modo esplicito una clausola ORDER:
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY CustomerID;
In questo caso, i risultati vengono restituiti nell'ordine specificato:
CustomerID
OrderCount
1005
1
1234
3
Le clausole in un'istruzione SELECT vengono applicate nell'ordine seguente:
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
Gli alias di colonna vengono assegnati nella clausola SELECT, che viene elaborata dopo la clausola GROUP BY ma prima della clausola ORDER BY. È possibile fare riferimento a un alias di colonna nella clausola ORDER BY, ma non nella clausola GROUP BY. La query seguente restituirà un errore di nome di colonna non valido:
SELECT CustomerID AS Customer,
COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY Customer
ORDER BY Customer;
Tuttavia, la query seguente avrà esito positivo, raggruppando e ordinando i risultati in base all'ID cliente.
SELECT CustomerID AS Customer,
COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY Customer;
Risoluzione degli errori di GROUP BY
Un ostacolo comune all'uso di GROUP BY nelle istruzioni SELECT è comprendere il motivo per cui si verifica il tipo di messaggio di errore seguente:
Messaggio 8120, livello 16, stato 1, riga 2 La colonna <column_name> non è valida nell'elenco SELECT poiché non è inclusa né in una funzione di aggregazione né nella clausola GROUP BY.
Ad esempio, la query seguente è consentita poiché ogni colonna nell'elenco SELECT è una colonna nella clausola GROUP BY o una funzione di aggregazione che opera su ogni gruppo:
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
La query seguente restituirà un errore poiché PurchaseOrderNumber non fa parte di GROUP BY e non viene usata con una funzione di aggregazione.
SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
La query restituisce l'errore:
Msg 8120, Level 16, State 1, Line 1
Column 'Sales.SalesOrderHeader.PurchaseOrderNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Tenere presente anche la considerazione seguente. Questa query restituisce una riga per ogni valore CustomerID. Tuttavia, poiché le righe per lo stesso CustomerID possono avere valori PurchaseOrderNumber diversi, quale valore deve essere restituito?
Per visualizzare gli ordini per ID cliente e per ordine di acquisto, è possibile aggiungere la colonna PurchaseOrderNumber alla clausola GROUP BY, come illustrato di seguito:
SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, PurchaseOrderNumber;
Questa query restituirà una riga per ogni cliente e ogni combinazione di ordini di acquisto, insieme al totale degli ordini per la combinazione.