Guida dettagliata alla funzione SQL COUNT()
In questa guida troverete spiegazioni dettagliate (corredate da esempi) di tutti gli utilizzi tipici della funzione CONTO() .
Conoscere l'aggregazione dei dati è un passo necessario per diventare un maestro di SQL. Questo include la funzione COUNT(), una delle funzioni di aggregazione più utilizzate in SQL.
L'aggregazione dei dati è ancora una conoscenza di base di SQL. Avere delle basi solide garantisce di non avere buchi nelle proprie conoscenze, facilitando così l'apprendimento di concetti SQL più complessi. Potete ottenere queste basi e molto altro ancora con il nostro percorso di apprendimento SQL dalla A alla Z. I suoi sette corsi interattivi vi daranno una struttura solida e renderanno sistematico il vostro apprendimento. Si inizia con le nozioni di base dell'SQL, come il recupero dei dati e l'utilizzo delle funzioni SQL standard (tra cui COUNT() e altre funzioni aggregate). Una volta acquisite queste nozioni, è molto più facile seguire i concetti più avanzati che questo percorso insegna, come le funzioni finestra, le espressioni di tabella comuni e le estensioni di GROUP BY.
Una volta appreso tutto questo, sarà necessario fare un po' di pratica per padroneggiarlo al meglio. A questo scopo, c'è la traccia Esercizi SQL con i suoi 88 esercizi interattivi.
Questo articolo sarà anche pratico e mostrerà diversi esempi di utilizzo di COUNT(). Per questo, abbiamo bisogno di un set di dati adatto.
Set di dati di esempio
Ci occuperemo di registi e dei loro film. La prima tabella è, senza sorpresa, denominata directors. Ecco la query che si può utilizzare per replicare questa tabella.
I dati della tabella si presentano così:
| id | first_name | last_name | date_of_birth | date_of_death | place_of_birth | country_of_birth |
|---|---|---|---|---|---|---|
| 1 | Ingmar | Bergman | 1918-07-14 | 2007-07-30 | Uppsala | Sweden |
| 2 | Lynne | Ramsay | 1969-12-05 | NULL | Glasgow | Scotland |
| 3 | Alejandro | Jodorowsky | 1929-02-07 | NULL | Tocophilla | Chile |
| 4 | Agnes | Varda | 1928-05-30 | 2019-03-29 | Brussels | Belgium |
| 5 | Pedro | Almodóvar | 1949-09-25 | NULL | Calzada de Calatrava | Spain |
| 6 | Chloé | Zhao | 1982-03-31 | NULL | Beijing | China |
| 7 | Jordan | Peele | 1979-02-21 | NULL | New York City | USA |
| 8 | Céline | Sciamma | 1978-11-12 | NULL | Pontoise | France |
| 9 | Jean-Luc | Godard | 1930-12-03 | 2022-09-13 | Paris | France |
| 10 | Stanley | Kubrick | 1928-07-26 | 1999-03-07 | New York City | USA |
Si tratta di un semplice elenco di registi con alcune informazioni su di loro.
La seconda tabella è films. È possibile creare anche questa tabella, utilizzando questa query. Ecco i dati della tabella:
| id | director_id | film_name | release_date | language |
|---|---|---|---|---|
| 1 | 7 | Get Out | 2017-01-23 | English |
| 2 | 2 | We Need to Talk About Kevin | 2011-05-12 | English |
| 3 | 2 | You Were Never Really Here | 2017-05-27 | English |
| 4 | 5 | The Skin I Live In | 2011-05-19 | Spanish |
| 5 | 7 | Us | 2019-03-08 | English |
| 6 | 2 | Ratcatcher | 1999-05-13 | English |
| 7 | 2 | Morvern Collar | 2002-11-01 | English |
| 8 | 3 | El Topo | 1970-12-18 | Spanish |
| 9 | 3 | The Holy Mountain | 1973-11-27 | Spanish |
| 10 | NULL | Dog Day Afternoon | 1975-09-20 | English |
| 11 | NULL | The Hater | 2020-03-06 | Polish |
| 12 | 1 | Cries and Whispers | 1972-12-21 | English |
È un elenco di film collegato alla tabella directors tramite la colonna director_id. Due valori di director_id sono NULL. Nel contesto del nostro set di dati, ciò significa che nella tabella sono presenti dati relativi a questi film films. Tuttavia, non c'è un regista corrispondente nella tabella. directors. In altre parole, abbiamo tutte le informazioni su "Quel pomeriggio di un giorno da cani" e "The Hater", tranne chi li ha diretti.
Ora che conosciamo i dati, possiamo occuparci di COUNT().
Che cos'è COUNT()?
La risposta è nel nome: la funzione COUNT() in SQL è usata per contare le righe. Conta le righe nell'insieme dei risultati, non nella tabella. Per essere più precisi, conta le righe nella tabella se la tabella è un insieme di risultati, cioè se i dati non sono stati filtrati in alcun modo. Se i dati sono filtrati, COUNT() restituisce il numero di righe nei dati filtrati.
Ecco un esempio:
SELECT COUNT(*) AS number_of_directorsFROM directors; |
L'asterisco (*) nella funzione COUNT() indica di contare tutte le righe. Poiché non vengono applicati filtri, l'intera tabella directors tabella sarà l'insieme dei risultati. La funzione COUNT() restituirà quindi il numero di righe della tabella:
| number_of_directors |
|---|
| 10 |
Il numero di righe è dieci, che in questo caso rappresenta anche il numero di amministratori.
Se l'insieme dei risultati è limitato, COUNT(*) restituirà un valore diverso. Ad esempio, si supponga di voler mostrare il numero di amministratori morti. Ciò significa contare solo gli amministratori che hanno una data nella colonna date_of_death. Quelli che hanno il valore NULL sono ancora vivi. Ecco la query:
SELECT COUNT(*) AS number_of_dead_directorsFROM directorsWHERE date_of_death IS NOT NULL; |
Abbiamo ottenuto ciò che volevamo filtrando i dati con la clausola WHERE. Ecco il risultato:
| number_of_dead_directors |
|---|
| 4 |
Quattro direttori non sono più in vita.
COUNT() e GROUP BY
Di solito, la funzione COUNT() viene utilizzata con la clausola GROUP BY. Per rinfrescare la memoria, GROUP BY è una clausola che raggruppa tutte le righe con lo stesso valore. Di solito, i gruppi sono colonne specifiche del set di dati. Per ulteriori informazioni, leggere questo articolo su come utilizzare GROUP BY.
Ecco un esempio di utilizzo di GROUP BY con COUNT(*):
SELECT country_of_birth, COUNT(*) AS number_of_directorsFROM directorsGROUP BY country_of_birthORDER BY country_of_birth; |
Vogliamo mostrare tutti i Paesi e il numero di direttori nati in essi. Selezioniamo il paese e usiamo COUNT(*) per mostrare il numero di registi. Poi specifichiamo la colonna country_of_birth in GROUP BY.
Ogni colonna della tabella che compare in SELECT deve comparire anche in GROUP BY. È logico, perché si vuole vedere il numero di registi per paese di nascita e mostrare questi paesi contemporaneamente.
Infine, l'output viene ordinato alfabeticamente per paese utilizzando ORDER BY country_of_birth:
| country_of_birth | number_of_directors |
|---|---|
| Belgium | 1 |
| Chile | 1 |
| China | 1 |
| France | 2 |
| Scotland | 1 |
| Spain | 1 |
| Sweden | 1 |
| USA | 2 |
C'è un regista per ogni paese, tranne che per la Francia e gli Stati Uniti.
Se avete bisogno di altri esempi, ecco l'articolo che mostra come utilizzare GROUP BY con le funzioni aggregate di SQL.
Uso di COUNT() con un'espressione o una colonna
Non ci si limita a scrivere un asterisco in COUNT(). Si può usare anche con una colonna o un'espressione, come l'istruzione CASE WHEN. La differenza è che conta con una colonna o un'espressione.
La differenza è che COUNT(expression) conta solo i valori nonNULL dell'espressione. COUNT(*) conterà anche i valori NULL.
A titolo di esempio, proviamo a elencare tutte le lingue cinematografiche e il numero di registi che hanno realizzato film in quelle lingue. Sembra che abbiamo tutti i dati nella tabella films.
Cosa succederebbe se usassimo COUNT(*)?
SELECT language, COUNT(*) AS number_of_directorsFROM filmsGROUP BY languageORDER BY language; |
| language | number_of_directors |
|---|---|
| English | 8 |
| Polish | 1 |
| Spanish | 3 |
In totale, questo output mostra tutti i 12 film presenti nella tabella. Attenzione: è sbagliato!
Il motivo? Ricordate che COUNT(*) conta il numero di righe nel dataset, compresi i NULL. Quindi questo risultato non rappresenta affatto il numero di registi! Poiché abbiamo contato le righe nella tabella filmsè evidente che abbiamo contato il numero di film, non di registi!
In altre parole, ci sono otto film in inglese, uno in polacco e tre in spagnolo. Questo risultato non ha nulla a che fare con il numero di registi!
Sarebbe un'idea migliore usare COUNT(director_id)? Proviamo:
SELECT language, COUNT(director_id) AS number_of_directorsFROM filmsGROUP BY languageORDER BY language; |
Selezioniamo le lingue e contiamo i registi tramite il loro identificatore: director_id. L'output è raggruppato e ordinato per lingua.
| language | number_of_directors |
|---|---|
| English | 7 |
| Polish | 0 |
| Spanish | 3 |
L'output è diverso da quello precedente, quindi analizziamolo.
Quello che abbiamo fatto bene contando director_id è che ci siamo liberati dei film con NULLs in director_id. Questo è particolarmente evidente per la lingua polacca: il numero di registi è pari a zero. Perché? Perché c'è un solo film polacco e ha un NULL in director_id, quindi non è stato contato.
Meno ovviamente, uno di questi film "manca" anche in inglese. Si tratta di Dog Day Afternoon, perché anch'esso ha un NULL in director_id.
In totale, il risultato mostra dieci registi perché ci sono dieci record nella tabella films con valori non nulli nella colonna director_id.
Ma se si torna alla tabella filmssi possono vedere alcuni ID di direttori che compaiono più volte. Quindi, sì, abbiamo mostrato il numero di amministratori, ma abbiamo anche incluso tutti gli amministratori ogni volta che compaiono nella tabella. In altre parole, abbiamo incluso i valori duplicati.
Questo risultato è più vicino a quello che volevamo ottenere, ma non è ancora del tutto corretto. Il conteggio degli ID duplicati degli amministratori gonfia il risultato, cioè mostra un numero irrealisticamente alto di singoli amministratori.
Il problema della duplicazione si risolve utilizzando COUNT() con DISTINCT.
COUNT(espressione) con DISTINCT
Dopo aver imparato a usare COUNT() con il nome di una colonna, è ora di imparare a usarlo con DISTINCT.
La clausola DISTINCT rimuove i duplicati. Quando viene usata con COUNT(expression), significa che la funzione conterà solo le istanze uniche di una colonna/espressione.
Utilizziamo lo stesso esempio di prima, ma con DISTINCT:
SELECT language, COUNT(DISTINCT director_id) AS number_of_directorsFROM filmsGROUP BY languageORDER BY language; |
Notate che DISTINCT è scritto nella funzione COUNT(). Viene prima della colonna che si vuole contare. Ecco i risultati:
| language | number_of_directors |
|---|---|
| English | 3 |
| Polish | 0 |
| Spanish | 2 |
Il risultato mostra tre registi con film in inglese, zero in polacco e due in spagnolo. Aspettate un attimo! Non è un risultato drasticamente diverso da quello ottenuto usando COUNT(director_id) senza DISTINCT?
Ecco il risultato precedente:
| language | number_of_directors |
|---|---|
| English | 7 |
| Polish | 0 |
| Spanish | 3 |
Vi rendete conto di cosa è successo qui? Senza DISTINCT, abbiamo contato tutti i valori di director_id. Quindi il modo corretto di trovare il numero di registi in questo esempio è usare COUNT() con DISTINCT.
Attenzione: Utilizzo di COUNT() con LEFT JOIN
Infine, utilizziamo entrambe le tabelle contemporaneamente. Immaginate di voler ottenere tutti i registi e il numero dei loro film.
Si pensa (correttamente) di aver bisogno di LEFT JOIN. È un buon inizio! Potrebbero esserci registi nella tabella directors che non hanno alcun film nella nostra films tabella.
Poiché vogliamo mostrare l'elenco di tutti i registi, LEFT JOIN è la scelta giusta. Per contare il numero di film, si potrebbe sentire il bisogno di usare COUNT(*). Ecco la query:
SELECT d.id, d.first_name, d.last_name, COUNT(*) AS number_of_filmsFROM directors dLEFT JOIN films fON d.id = f.director_idGROUP BY d.id, d.first_name, d.last_nameORDER BY d.id; |
Abbiamo selezionato le colonne necessarie e abbiamo usato COUNT(*). Le due tabelle sono unite sulla colonna contenente gli ID dei registi.
Il risultato è raggruppato in base agli ID e ai nomi dei registi e ordinato in base all'ID. E i risultati:
| id | first_name | last_name | number_of_films |
|---|---|---|---|
| 1 | Ingmar | Bergman | 1 |
| 2 | Lynne | Ramsay | 4 |
| 3 | Alejandro | Jodorowsky | 2 |
| 4 | Agnes | Varda | 1 |
| 5 | Pedro | Almodóvar | 1 |
| 6 | Chloé | Zhao | 1 |
| 7 | Jordan | Peele | 2 |
| 8 | Céline | Sciamma | 1 |
| 9 | Jean-Luc | Godard | 1 |
| 10 | Stanley | Kubrick | 1 |
Sembra che ogni regista abbia almeno un film. In totale, sono 15 film. Aspettate! Non mi sembra giusto! Ci sono solo 12 film nella tabella films.
Inoltre, sappiamo per certo che non ci sono film di Stanley Kubrick nella tabella. Come mai l'output mostra che ne ha uno? Anche i dati relativi a tutti gli altri registi sono sbagliati? Sì. L'uso di COUNT(*) non è la soluzione giusta in questo caso. LEFT JOIN restituirà una riga per tutti i registi, anche quelli che non sono stati trovati nella tabella films. E COUNT(*) conta tutte le righe, anche quelle che non hanno film corrispondenti.
Invece di COUNT(*), utilizzare COUNT() con il nome della colonna. Quale colonna dobbiamo contare? Il modo più sicuro è quello di contare gli identificatori univoci, ovvero l'id della colonna nella tabella films.
SELECT d.id, d.first_name, d.last_name, COUNT(f.id) AS number_of_filmsFROM directors dLEFT JOIN films fON d.id = f.director_idGROUP BY d.id, d.first_name, d.last_name; |
La query è praticamente la stessa di prima, a parte l'uso diverso di COUNT(). E questo è il risultato:
| id | first_name | last_name | number_of_films |
|---|---|---|---|
| 1 | Ingmar | Bergman | 1 |
| 2 | Lynne | Ramsay | 4 |
| 3 | Alejandro | Jodorowsky | 2 |
| 4 | Agnes | Varda | 0 |
| 5 | Pedro | Almodóvar | 1 |
| 6 | Chloé | Zhao | 0 |
| 7 | Jordan | Peele | 2 |
| 8 | Céline | Sciamma | 0 |
| 9 | Jean-Luc | Godard | 0 |
| 10 | Stanley | Kubrick | 0 |
Questo è più che sufficiente! Se si sommano i valori, si vede che ci sono dieci film in totale. Perché non 12? Perché due film sono di registi che non esistono nel nostro set di dati, cioè hanno dei NULL nella colonna director_id nella tabella films.
Per consolidare quanto appreso, date un'occhiata ad altri esempi di utilizzo di COUNT().
COUNT() è facile come uno, due, tre!
Non è stato difficile, vero? Questi semplici esempi vi hanno mostrato tutte le varianti di utilizzo di COUNT(). La funzione in sé non è difficile da capire. Ma, come avete visto, ci sono diversi modi di usarla e ognuno di essi può restituire un risultato diverso.
Scegliere come usare COUNT() diventa più facile con la pratica. La funzione Esercizi SQL è stato progettato proprio per questo scopo. Potete anche provare questi sette esempi della funzione COUNT().
Oltre alla pratica, è importante conoscere i propri dati e ciò che si vuole ottenere con essi. Quando tutto questo vi sarà chiaro, COUNT() diventerà davvero una funzione facile. Potete contarci! Il gioco di parole è voluto.
