Riepilogare i dati con GROUP BY
Sebbene le funzioni di aggregazione siano utili per l'analisi, è possibile disporre i dati in subset prima di riepilogarli. In questa sezione si apprenderà come eseguire questa operazione usando la clausola GROUP BY.
Utilizzo della clausola GROUP BY
Come si è appreso, 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 utilizzare 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 come determinato dagli elementi specificati nella clausola .
Ad esempio, la query seguente genera un set di righe raggruppate, una riga per CustomerID nella tabella Sales.SalesOrderHeader . Un altro modo per esaminare il processo GROUP BY è che tutte le righe con lo stesso valore per CustomerID verranno raggruppate e restituite in una singola riga di risultato.
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 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 vengono visualizzati nell'elenco GROUP BY. Questo requisito avrà un impatto sulla scrittura delle clausole SELECT e HAVING.
Qual è quindi la differenza tra la scrittura della query con group by o distinct? Se si vuole sapere tutti 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;
Per ogni valore CustomerID , la query aggrega e conta le righe, quindi viene illustrato il numero di righe nella tabella SalesOrderHeader a ogni cliente.
ID Cliente
OrderCount
1234
3
1005
1
Si noti che GROUP BY non garantisce l'ordine dei risultati. Spesso, in seguito alla modalità di esecuzione dell'operazione di raggruppamento da parte di Query Processor, i risultati vengono restituiti nell'ordine dei valori del gruppo. Tuttavia, non è consigliabile basarsi su questo comportamento. Se è necessario ordinare i risultati, è necessario includere in modo esplicito una clausola ORDER:
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY CustomerID;
Questa volta, i risultati vengono restituiti nell'ordine specificato:
ID Cliente
OrderCount
1005
1
1234
3
Le clausole in un'istruzione SELECT vengono applicate nell'ordine seguente:
- DA
- WHERE
- RAGGRUPPA PER
- AVENDO
- SELEZIONA
- ORDER BY
Gli alias di colonna vengono assegnati nella clausola SELECT, che si verifica 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 genererà 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 GROUP BY
Un ostacolo comune all'uso di GROUP BY nelle istruzioni SELECT è comprendere perché si verifica il tipo di messaggio di errore seguente:
Msg 8120, Level 16, State 1, Line 2 Column <column_name> non è valido nell'elenco di selezione perché non è contenuto in una funzione di aggregazione o nella clausola GROUP BY.
Ad esempio, la query seguente è consentita perché 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 perché PurchaseOrderNumber non fa parte di GROUP BY e non viene usato con una funzione di aggregazione.
SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
Questa 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.
Ecco un altro modo per pensarci. Questa query restituisce una riga per ogni valore CustomerID . Ma le righe per lo stesso CustomerID possono avere valori PurchaseOrderNumber diversi, quindi quale dei valori è quello che deve essere restituito?
Se si desidera visualizzare gli ordini per ID cliente e per ordine di acquisto, è possibile aggiungere la colonna PurchaseOrderNumber alla clausola GROUP BY, come indicato 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 conteggio degli ordini per tale combinazione.