Come utilizzare la clausola GROUP BY

GROUP BY è una parte importante dell'istruzione SQL SELECT. Ma i nuovi codificatori SQL possono incorrere in alcuni problemi quando questa clausola viene usata in modo non corretto. Ecco come evitare questi problemi.

State imparando l'SQL. Sapete come selezionare alcuni dati da una tabella e come filtrare i dati con una clausola WHERE. Potete elaborare i dati utilizzando le funzioni aggregate (MIN, MAX, SUM, AVG e altre). Ma quando si ha a che fare con molti dati, potrebbe essere necessario restringerli ulteriormente. È qui che entra in gioco la clausola GROUP BY, che consente di organizzare le informazioni in base ai parametri impostati.

In questo articolo spiegheremo come utilizzare la clausola GROUP BY. Parleremo anche di un paio di possibili problemi e di come evitarli o risolverli.

Utilizzo di GROUP BY

Per capire come utilizzare GROUP BY, è necessario definire una tabella su cui esercitarsi. Che ne dite di una che descriva le persone?

| ID | Name     | Gender | Height | Weight | Eye_color |
--------------------------------------------------------
|  1 | Mark     | Male   |    180 |     78 | Blue      |
|  2 | Susan    | Female |    172 |     59 | Brown     |
|  3 | Thomas   | Male   |    179 |     92 | Brown     |
|  4 | Katarina | Female |    164 |     53 | Green     |
|  5 | Mindy    | Female |    170 |     58 | Blue      |
--------------------------------------------------------

Abbiamo così la nostra tabella di esercitazione. Ora entriamo nel vivo delle statistiche. Ad esempio, qual è l'altezza media di tutte le persone? Per scoprirlo, digitiamo:

SELECT AVG(Height)
FROM People

+-------------+
| AVG(Height) |
+-------------+
|    173.0000 |
+-------------+

Supponiamo ora di voler conoscere l'altezza media in base al sesso. Sembra abbastanza facile: basta aggiungere una clausola WHERE. Quindi digitiamo:

SELECT AVG(Height)
FROM People
WHERE Gender = ‘Male’

Ma cosa succede se introduciamo altri generi nella nostra tabella? In questo caso, dovremmo scrivere altre query e raccogliere manualmente i dati di cui abbiamo bisogno. È più semplice raggruppare i dati in base a Gender e calcolare l'altezza media per ciascun gruppo, come illustrato di seguito.

SELECT Gender, AVG(Height)
FROM People
GROUP BY Gender

+--------+-------------+
| Gender | AVG(Height) |
+--------+-------------+
| Female |    168.6667 |
| Male   |    179.5000 |
+--------+-------------+

Raggruppare sembra facile, vero? Basta aggiungere la clausola GROUP BY all'istruzione SQL. Supponiamo però di avere due parametri nella nostra ricerca. In questo caso, dobbiamo raggruppare per due colonne. Supponiamo di voler sapere quanti uomini e donne hanno gli occhi azzurri, marroni o verdi. Dovremmo digitare:

SELECT Gender, Eye_color, COUNT(*)
FROM People
GROUP BY Gender, Eye_color

+--------+-----------+----------+
| Gender | Eye_color | COUNT(*) |
+--------+-----------+----------+
| Female | Blue      |        1 |
| Female | Brown     |        1 |
| Female | Green     |        1 |
| Male   | Blue      |        1 |
| Male   | Brown     |        1 |
+--------+-----------+----------+

Questa è solo un'introduzione a GROUP BY. Si può usare in molti modi. Perché non provare a sperimentare diverse funzioni aggregate (come AVG e COUNT) per acquisire una migliore padronanza di GROUP BY?

Errori comuni di GROUP BY

Anche se GROUP BY sembra abbastanza facile da usare, è frequente che i neofiti di SQL si trovino di fronte a messaggi di errore confusi. Eccone alcuni in cui ci imbattiamo spesso:

1. Selezione di più valori

La cosa più importante da ricordare quando si usa GROUP BY è che qualsiasi cosa si stia per selezionare deve essere un singolo valore. Ecco perché è necessario utilizzare una funzione aggregata: essa prende più valori e ne produce uno solo da restituire.

Per essere un po' più chiari, diamo un'occhiata ai nostri gruppi:

+--------+--------+
| Gender | Height |
+--------+--------+
| Male   |    180 |
|        |    179 |
| Female |    172 |
|        |    164 |
|        |    170 |
+--------+--------+

Quando chiediamo Altezza raggruppata per Gender, vogliamo ottenere un singolo valore. Ma qui Male ha due valori per l'altezza e Female ne ha tre. Quale dovremmo scegliere?

SELECT Gender, Height
FROM People
GROUP BY Gender;

(MYSQL) ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains 
nonaggregated column 'vertabelo.People.Height' which is not functionally dependent on columns 
in GROUP BY clause;

Ecco perché dobbiamo usare una funzione aggregata (come AVG, che abbiamo usato in precedenza) per ottenere un singolo valore.

2. Usare WHERE per filtrare i risultati

Supponiamo di voler vedere i risultati del colore degli occhi solo per i gruppi di persone di altezza superiore a 170 cm. Se proviamo a inserirlo nella clausola WHERE, come mostrato di seguito:

SELECT Gender, Eye_color, COUNT(*)
FROM People
WHERE AVG(Height) > 170
GROUP BY Gender, Eye_color

Otteniamo il messaggio di errore riportato di seguito:

(MYSQL) ERROR 1111 (HY000): Invalid use of group function

Questo perché il database raggruppa i record dopo averli filtrati. Vogliamo filtrare il risultato dell'istruzione GROUP BY. A tale scopo, si utilizza un'altra clausola chiamata HAVING. Viene dopo GROUP BY e funziona come WHERE. La differenza è che in essa si possono usare funzioni aggregate. Per ottenere l'insieme di risultati desiderato, scriveremo questo codice:

SELECT Gender, Eye_color, COUNT(*)
FROM People
GROUP BY Gender, Eye_color
HAVING AVG(Height) > 170

Ricordate che nel database, un'istruzione SQL SELECT viene sempre eseguita in questo ordine:

1. FROM
2. DOVE
3. GRUPPO PER
4. AVENTE
5. SELEZIONARE
6. ORDINARE PER

Prendiamo un'origine dati, filtriamo i record, li raggruppiamo, filtriamo i gruppi, selezioniamo le colonne desiderate e le ordiniamo.

3. Errori di entità multiple

Questo è un caso un po' più avanzato. Quando abbiamo unito più tabelle, è possibile che alcune entità abbiano gli stessi nomi. Pertanto, spesso i risultati devono essere raggruppati in base all'ID dell'entità piuttosto che al suo nome.

Ad esempio, si supponga di voler esaminare i dati della città di Varsavia. C'è una Varsavia che è la capitale della Polonia e una Varsavia che si trova nello stato dell'Indiana, negli Stati Uniti.

Quindi diciamo che abbiamo una tabella definita in questo modo:

Table City:
---------------------
| ID | Name | Award | 
---------------------

Questa tabella descrive le città che hanno ricevuto uno o più premi. Una città è identificata dal suo ID e può avere molti riconoscimenti.

Se si volesse vedere il numero di premi ricevuti da una città e avere queste informazioni raggruppate per nome della città, si potrebbe usare questa query:

SELECT City.Name, COUNT(Award)
FROM City
GROUP BY City.Name

Il problema è che utilizzando City.Name, i premi di Varsavia, Polonia e Varsavia, Indiana, verrebbero sommati. Dopo tutto, entrambe sono Varsavia! Tuttavia, si tratta di luoghi diversi, e come tali hanno valori diversi di City.ID. Se si pensa a una città come entità del database, essa viene identificata dal suo ID piuttosto che dai suoi attributi (come Name). Se raggruppiamo i risultati per ID, otterremo le informazioni corrette. Poiché vogliamo comunque visualizzare il nome, useremo qualcosa di simile:

SELECT City.Name, COUNT(Award)
FROM City
GROUP BY City.ID

Il risultato avrà voci separate per le diverse Warsaws con i valori desiderati. (Normalmente, ci sarebbe un'altra colonna, come "paese" o "stato", per differenziare le due città. Ma a titolo di esempio, diciamo che non c'è).

4. Utilizzo di valori non aggregati

Nell'esempio precedente, abbiamo selezionato l'attributo City.Name e abbiamo raggruppato i risultati in base all'attributo City.ID. Nella nostra tabella, ogni record con lo stesso ID ha anche lo stesso nome di città. Alcuni database non avranno problemi e restituiranno i risultati attesi, ma altri daranno un errore dicendo che City.Name non è nella clausola GROUP BY e che non è aggregato. Di seguito ho riprodotto il messaggio di errore di MySQL:

(MYSQL) ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains 
nonaggregated column 'vertabelo.City.Name' which is not functionally dependent on columns 
in GROUP BY clause;

Ma è un valore singolo! Come possiamo risolvere questo errore? Dal momento che un City.ID significa esattamente un City.Name, possiamo inserirli entrambi nella clausola GROUP BY:

SELECT City.Name, COUNT(*)
FROM City
JOIN Person ON (Person.CityID = City.ID)
GROUP BY City.ID, City.Name

Questo dovrebbe risolvere il problema.

La pratica rende perfetti

Se non avete ancora capito come e quando usare la clausola GROUP BY o se volete esercitarvi a usarla, date un'occhiata al nostro corso SQL Queries. Copre tutte le nozioni di base e quelle più avanzate, per cui avrete molte opportunità di perfezionare le vostre competenze in SQL.