Come usare GROUP BY e ORDER BY nella stessa query: Una guida dettagliata

Come utilizzare due clausole SQL di base - GROUP BY e ORDER BY - in un'unica query.

GROUP BY e ORDER BY sono clausole SQL di base. Come tali, vengono insegnate relativamente presto nel processo di apprendimento. E di solito non rappresentano un grosso ostacolo per i principianti. Tuttavia, l'uso congiunto di GROUP BY e ORDER BY può creare confusione. Alla fine di questo articolo, la vostra confusione su questo argomento sparirà.

Se volete fare pratica con l'uso di GROUP BY e ORDER BY, il nostro corso interattivo vi aiuterà. SQL Basics interattivo vi aiuterà. Come dice il nome, copre le basi di SQL. In questo modo sarà più facile seguire gli argomenti che presenteremo nell'articolo.

Il corso stesso contiene 129 esercizi per esercitarsi sui concetti di base di SQL. Questi concetti includono l'estrazione di dati da una o più tabelle (JOIN) e il filtraggio dei dati utilizzando WHERE e operatori come LIKE, ILIKE, IN e BETWEEN. Naturalmente, GROUP BY è presente, così come le operazioni di set UNION, INTERSECT, e EXCEPT.

Cosa sono GROUP BY e ORDER BY in SQL?

GROUP BY e ORDER BY non sono la stessa cosa, questo è ovvio!

La clausola SQL GROUP BY raggruppa le righe di dati con gli stessi valori nelle colonne specificate. Questa clausola viene utilizzata più comunemente con le funzioni aggregate di SQL per calcolare statistiche (come il conteggio di determinati valori, la somma, la media e il valore minimo/massimo in un insieme) per un gruppo di righe. Ecco un semplice esempio:

SELECT 
  country,
  COUNT(*)
FROM box_office
GROUP BY country;

La query chiede al database di raggruppare i dati in base ai valori della colonna country. Poi mostra il nome del paese e conta le righe. Questo ci dirà quanti record della tabella appartengono a ciascun paese.

Parliamo ora di ORDER BY. Questo comando ordina il risultato della query in ordine crescente (da 1 a 10, da A a Z) o decrescente (da 10 a 1, da Z ad A). L'ordinamento ascendente è quello predefinito; se si omette la parola chiave ASCending o DESCending, la query verrà ordinata in ordine ascendente. È possibile specificare l'ordine di ordinamento utilizzando ASC o DESC. Ecco un semplice esempio:

SELECT 
  movie,
  city,
  gross
FROM box_office
ORDER BY gross;

Questa query seleziona il nome del film, la città di proiezione e gli incassi. Quindi l'output viene ordinato in base al guadagno lordo utilizzando la clausola ORDER BY.

Questo è qualcosa che probabilmente conoscete. Vediamo ora come queste clausole SQL possono essere utilizzate insieme. Potrebbe essere utile avere a portata di mano questo articolo sul funzionamento di GROUP BY o la guida su ORDER BY, nel caso in cui vi servano chiarimenti su qualcosa che abbiamo menzionato in questo articolo.

Esempio di dati

Negli esempi che seguono utilizzeremo una tabella denominata box_office negli esempi che seguono. È possibile crearla utilizzando questa query.

Si tratta di una panoramica dei guadagni al botteghino di due film: "Beau ha paura" e "The French Dispatch".

I dati mostrano i guadagni (in dollari USA) dei cinema di due Paesi - Spagna e Paesi Bassi - e delle rispettive città: Amsterdam, Rotterdam, Den Haag, Barcellona, Madrid e Valencia.

GROUP BY e ORDER BY di una colonna non aggregata in SELECT

Cominciamo con l'esempio più semplice di utilizzo di GROUP BY e ORDER BY in una query. Vogliamo mostrare l'elenco dei film in ordine alfabetico.

Ecco come raggruppare per una colonna e ordinare il risultato per una colonna:

SELECT movie
FROM box_office
GROUP BY movie
ORDER BY movie ASC; 

La query seleziona il nome del film dalla tabella box_office. Occorre quindi utilizzare GROUP BY. Senza questo, l'output sarebbe costituito da tutte le dodici righe della tabella, che mostrano i nomi ripetuti degli stessi due film della tabella box_office. Vogliamo che ogni film appaia solo una volta, perché questo è lo scopo dell'elenco. Per raggruppare i dati è sufficiente specificare la colonna per la quale si vuole raggruppare nella clausola GROUP BY.

L'output viene ordinato con la clausola ORDER BY. Come per GROUP BY, nella clausola si specifica il nome della colonna. Vogliamo che l'output sia ordinato in ordine alfabetico, quindi il nome della colonna è seguito da ASC.

Utilizziamo questa immagine dell'ordine di esecuzione della query SQL per visualizzare come il database arriva al risultato.

La query ottiene innanzitutto la tabella nella clausola FROM. Poi raggruppa i dati in base alla colonna specificata in GROUP BY. Successivamente, seleziona la colonna nell'istruzione SELECT. Solo alla fine di tutto ciò, l'output viene ordinato in base alla clausola ORDER BY.

In altre parole, i dati vengono prima raggruppati e poi ordinati. Questo ci porta a una nota importante: quando si utilizzano GROUP BY e ORDER BY in una query, GROUP BY deve sempre, senza eccezioni, essere scritto prima di ORDER BY!

Diamo un'occhiata all'output della query.

Come si può vedere, si tratta di un elenco che mostra i nostri due film in ordine alfabetico.

GROUP BY e ORDER BY di una colonna aggregata in SELECT

È possibile utilizzare GROUP BY insieme a ORDER BY quando si ordina in base a una funzione aggregata mostrata in SELECT. Ecco un esempio:

SELECT 
  movie,
  SUM(gross) AS gross_per_movie
FROM box_office
GROUP BY movie
ORDER BY gross_per_movie DESC;

La query seleziona un film dalla tabella. Quindi si utilizza la funzione aggregata SUM() per sommare i guadagni lordi del film. Poiché vogliamo mostrare i guadagni lordi per film, dobbiamo raggruppare l'output per nome del film. Come ultimo passo, vogliamo ordinare i risultati dal guadagno più alto a quello più basso. È possibile farlo elencando la colonna aggregata in ORDER BY. Questo deve essere seguito dalla parola chiave DESC per ordinare dal più alto al più basso.

Si noti che si utilizza l'alias gross_per_movie per la colonna aggregata in ORDER BY. Avremmo potuto usare anche l'espressione aggregata completa:

SELECT 
  movie,
  SUM(gross) AS gross_per_movie
FROM box_office
GROUP BY movie
ORDER BY SUM(gross) DESC;

Ecco come appare l'output:

Mostra due film e i loro guadagni in ordine decrescente.

GROUP BY e ORDER BY per una colonna non aggregata non in SELECT

Nel primo esempio, abbiamo ordinato l'output in base alla colonna mostrata in SELECT. Proviamo ora a ordinare i dati in base a una colonna non presente in SELECT:

SELECT SUM(gross) AS gross_by_city
FROM box_office
GROUP BY city
ORDER BY city ASC;

In questa query, utilizziamo la funzione SUM() per calcolare i guadagni lordi per città. Tuttavia, in SELECT non c'è la colonna città. Questo perché vogliamo vedere solo i valori lordi. Raggruppiamo comunque l'output in base alla città, come faremmo se questa colonna fosse presente in SELECT. Infine, ordiniamo l'output in ordine alfabetico per città.

L'output è mostrato di seguito:

Come si può vedere, la query funziona. Ricordate: è possibile ordinare l'output in base a una colonna non aggregata non mostrata in SELECT, ma solo se tale colonna è elencata in GROUP BY.

Ciò significa che i dati possono essere raggruppati anche in base a una colonna non mostrata in . SELECT. La colonna può essere elencata in SELECT ma non in GROUP BY? No! Si deve ricordare questa regola: se la colonna non raggruppata appare in SELECTdeve comparire anche in GROUP BY.

GROUP BY e ORDER BY di una colonna aggregata non presente in SELECT

Modifichiamo un po' l'esempio precedente e proviamo a ordinare i dati in base a una colonna aggregata non presente in SELECT.

Ecco il codice. Si cerca di mostrare un elenco di città ordinate in base ai guadagni lordi:

SELECT city
FROM box_office
GROUP BY city
ORDER BY SUM(gross);

Selezioniamo la colonna city e raggruppiamo l'output in base ad essa.

Poi utilizziamo SUM() in ORDER BY in modo da poter ordinare l'output in base ai guadagni per città. L'ordinamento è crescente.

Vediamo se la query funziona:

Sì, funziona! Ecco un'altra nota: è possibile ordinare l'output in base a una colonna aggregata non mostrata nel file SELECT.

GROUP BY e ORDER BY per più colonne

Finora abbiamo raggruppato i dati in base a una sola colonna. Vediamo come raggruppare i dati in base a più colonne.

Mostriamo ogni città e il suo paese:

SELECT 
  city,
  country,
  AVG(gross) AS average_gross
FROM box_office
GROUP BY city, country
ORDER BY city;

Selezioniamo la città e il paese. Successivamente, utilizziamo AVG() per calcolare il guadagno lordo medio per città. Poi elenchiamo le stesse colonne in GROUP BY. Come avete imparato, ogni colonna che compare in SELECT deve comparire anche in GROUP BY. Quando elenchiamo più colonne in GROUP BY, le separiamo semplicemente con una virgola.

Infine, ordiniamo il risultato per città in ordine crescente:

La domanda sorge spontanea: Se è possibile GROUP BY più colonne, è possibile anche ORDER BY più di una colonna?

Proviamo:

SELECT city,
	 country,
	 AVG(gross) AS average_gross
FROM box_office
GROUP BY city, country
ORDER BY country, city;

Il principio è lo stesso di GROUP BY: basta elencare le colonne in ORDER BY e separarle con una virgola.

Ecco cosa restituisce la query:

Sì, l'output è davvero ordinato per paese e poi per ogni città all'interno del paese.

Colonna aggregata sia in GROUP BY che in ORDER BY

Abbiamo provato tutte le combinazioni, ma non abbiamo provato a GROUP BY e ORDER BY più colonne, di cui una aggregata. Proviamo a farlo:

SELECT 
  city,
  AVG(gross) AS average_gross_by_city
FROM box_office
GROUP BY city, AVG(gross)
ORDER BY city, AVG(gross);

Questa query vuole mostrare il guadagno medio lordo per città. Elenchiamo la città in SELECT e usiamo la funzione AVG(). Poi raggruppiamo per città e per la colonna aggregata. Infine, l'output viene ordinato in base alla stessa colonna.

Tuttavia, l'unico risultato ottenuto è questo errore.

Il messaggio di errore significa letteralmente quello che dice: le funzioni aggregate non sono consentite in GROUP BY. A cosa servirebbe usarle in GROUP BY? La funzione aggregate aggrega i dati a una riga e GROUP BY non può fare altro.

Utilizzo di GROUP BY con HAVING e ORDER BY

GROUP BY e ORDER BY non sono le uniche clausole che possono essere utilizzate in una query: possiamo anche aggiungere HAVING.

Esaminiamo il codice seguente per vedere come funziona.

SELECT 
  city,
  SUM(gross) AS gross_per_city
FROM box_office
GROUP BY city
HAVING SUM(gross) > 800000
ORDER BY SUM(gross) > 800000 DESC;

Questa query dovrebbe:

• Restituire le città con guadagni lordi superiori a 800.000 dollari.
• Mostrare l'importo guadagnato da quella città.
• Ordinare il risultato in base al guadagno lordo in ordine decrescente.

Per farlo, selezioniamo la colonna città e utilizziamo la funzione SUM(). Quindi si raggruppa l'output per città. Successivamente, si utilizza la clausola HAVING per mostrare solo le città con guadagni superiori a 800.000 dollari. Utilizziamo semplicemente la colonna aggregata di SELECT e la confrontiamo con la condizione utilizzando l'operatore di confronto '>'. Poi usiamo lo stesso operatore in ORDER BY per ordinare l'output in modo decrescente.

La regola è la seguente: La clausola HAVING va sempre scritta dopo GROUP BY ma prima di ORDER BY.

È possibile rivedere l'immagine dell'ordine di esecuzione delle query SQL per verificare questo aspetto. Si vedrà che HAVING viene eseguita dopo GROUP BY ma prima di ORDER BY. In altre parole, i dati vengono prima raggruppati, poi filtrati e infine ordinati.

Ecco il risultato della query:

Si può notare che ci sono solo due città in cui i nostri due film hanno guadagnato più di 800.000 dollari: Amsterdam e Madrid.

Esercitarsi a usare GROUP BY con ORDER BY

Questo articolo vi ha mostrato che GROUP BY e ORDER BY possono essere utilizzati nella stessa query SQL. Non solo, ma possono essere utilizzati in più modi.

Nel frattempo, abbiamo imparato alcune regole importanti:

1. GROUP BY viene prima di ORDER BY.
2. L'output può essere ordinato in base a una colonna non aggregata non mostrata in SELECT solo se la colonna compare in GROUP BY.
3. I dati possono essere raggruppati in base alla colonna non aggregata non mostrata in SELECT.
4. Qualsiasi colonna non aggregata presente in SELECT deve essere elencata in GROUP BY.
5. L'output può essere ordinato da una colonna aggregata non mostrata in SELECT.
6. Una colonna aggregata non è ammessa in GROUP BY.
7. HAVING viene dopo GROUP BY ma prima di ORDER BY.

Anche se vi abbiamo spiegato queste regole e vi sembra di aver imparato qualcosa, questo non è sufficiente.

Per conservare queste conoscenze, è necessario fare pratica con GROUP BY e ORDER BY, soprattutto all'inizio del vostro percorso in SQL. Fare pratica in SQL significa scrivere molto codice. Solo così potrete consolidare le vostre conoscenze. Il nostro corso interattivo SQL Basics Il nostro corso interattivo offre 129 esercizi pratici di SQL: un sacco di spazio per esercitarsi!