Come utilizzare CASE WHEN con SUM() in SQL

Questo articolo vi insegnerà cos'è un'espressione CASE WHEN in SQL e come utilizzarla con la funzione SUM() e l'istruzione GROUP BY. Gli esempi sono inclusi per rafforzare la comprensione.

Saper usare l'espressione CASE WHEN in SQL permette di comprendere le interessanti possibilità offerte da SQL. Vi permette di passare da un uso passivo di SQL ad avere padronanza delle vostre ricerche dove potrete impostare criteri specifici in SQL. In combinazione con altre funzioni e istruzioni, come SUM() e GROUP BY, l'espressione CASE WHEN è uno strumento potente per creare rapporti SQL complessi.

Potete imparare a conoscere CASE WHEN e a usarlo con SUM() e GROUP BY nel nostro corso pratico Creating Basic SQL Reports. Per esercitarvi ulteriormente con SQL, consultate il nostro SQL Practice con oltre 500 esercizi interattivi.

CASE, CASE WHEN, CASE WHEN THEN o CASE WHEN THEN ELSE?

Generalmente, in SQL questa espressione viene chiamata CASE, a volte può essere denominata CASE WHEN o una qualsiasi delle altre espressioni citate in precedenza. Non lasciatevi confondere: è la stessa cosa. Le clausole WHEN, THEN e ELSE fanno tutte parte dell'istruzione CASE.

Come funziona un'espressione CASE WHEN?

Consideratela l'equivalente SQL del costrutto IF-THEN-ELSE. Forse lo conoscete già, soprattutto se l'avete usato in Excel o in altri linguaggi di programmazione. In ogni caso, rivediamolo qui.

Il costrutto IF-THEN-ELSE funziona così. L'istruzione IF esegue un test logico; verifica se un'espressione specifica è vera o falsa. Se l'espressione è vera, assegna un determinato valore specificato dall'utente, altrimenti assegna un valore diverso, anch'esso specificato dall'utente.

La struttura dell'espressione CASE WHEN è la stessa. Esegue un test logico; nel caso in cui l'espressione sia vera, le assegnerà un valore specifico. In caso contrario, assegnerà un valore diverso.

Vi mostrerò la logica e la sintassi di CASE WHEN in un esempio.

Esempio 1: L'espressione CASE WHEN

Abbiamo una tabella chiamata test_result che contiene i punteggi dei test. Le colonne sono:

• id: il numero del documento d'identità dello studente.
• first_name: il nome dello studente.
• last_name: il cognome dello studente.
• score: il punteggio del test.

Il compito è quello di assegnare le categorie dei risultati del test in base al punteggio. Ecco come fare:

SELECT	
  first_name,
  last_name,
  score,
  CASE
    WHEN score > 90 THEN 'Exceptional result'
    WHEN score > 70 THEN 'Great result'
    WHEN score > 50 THEN 'Average result'
    ELSE 'Poor result'
  END AS score_category
FROM test_result
ORDER BY score DESC;

L'istruzione SELECT seleziona il nome e il cognome degli studenti e i loro punteggi dalla tabella, test_result, fin qui nulla di strano. Ora inizia la parte divertente! L'istruzione CASE inizia con la parola chiave CASE, naturalmente. Dopodiché, definisco le condizioni da verificare con l'istruzione CASE e i valori da assegnare; per farlo, utilizzo WHEN e THEN. Ad esempio, se il punteggio è superiore a 90, viene classificato come "Risultato eccezionale". Se è superiore a 70, è un "Risultato ottimo". Non è necessario indicare esplicitamente "e minori di 90"; SQL tiene conto di altre condizioni per evitare risultati duplicati o errori.

Seguendo la stessa logica, ogni punteggio superiore a 50 è considerato un "Risultato mediocre". Qualsiasi punteggio che non soddisfi una delle tre condizioni cui sopra riportate viene classificato come "Risultato scarso". Ricordatevi che ELSE viene usato per assegnare il valore quando nessuna delle condizioni definite da CASE e WHEN è soddisfatta.

L'istruzione CASE è chiusa dall'istruzione END, dopo di che, è possibile definire il nome della colonna in cui saranno memorizzati i risultati dell'istruzione CASE; in questo caso, si tratta della colonna score_category. Ho inoltre ordinato i risultati in ordine decrescente in base al punteggio della colonna. Sicuramente vorrete vedere il risultato:

Si noti che la condizione ELSE non è obbligatoria in un'istruzione CASE. Proviamo a ometterla. Ecco lo stesso codice di prima, ma senza la condizione ELSE:

SELECT	
  first_name,
  last_name,
  score,
  CASE
    WHEN score > 90 THEN 'Exceptional result'
    WHEN score > 70 THEN 'Great result'
    WHEN score > 50 THEN 'Average result'
  END AS score_category
FROM test_result
ORDER BY score DESC;

Eseguite il codice ed esaminatene il risultato:

Il codice è stato eseguito senza errori, ma il risultato è diverso. Non c'è più la categoria "Risultato scarso", ci sono invece i valori NULL. Ricordate: quando le righe non corrispondono a nessuna delle condizioni definite, l'istruzione CASE restituirà il valore NULL.

Se volete approfondire la sintassi, questo articolo molto approfondito sulla logica di CASE WHEN potrebbe esservi utile.

Esempio 2: L'espressione CASE WHEN con una SUM() e un GROUP BY

Ora facciamo sul serio! L'espressione CASE WHEN viene spesso utilizzata con una funzione SUM() in report più complessi, che possono essere piuttosto impegnativi per i principianti. Anche se probabilmente siete abituati a usare la funzione SUM() per sommare valori, essa può essere usata anche per contare.

Questo esempio vi aiuterà a capire meglio il concetto. Utilizzerò la tabella subject che ha le seguenti colonne:

• id: il numero identificativo della materia.
• name: il nome della materia.
• number_of_lectures: il numero di lezioni durante l'anno.
• department: il dipartimento in cui viene insegnata la materia.

Il compito è quello di contare il numero di materie obbligatorie e ottative per dipartimento. In questo esempio, ogni materia che ha più di 20 lezioni durante l'anno è considerata obbligatoria. Sapete come risolvere questo compito? Lasciate che vi aiuti:

SELECT	
  department,
  SUM (CASE
    WHEN number_of_lectures > 20 THEN 1
    ELSE 0
  END) AS mandatory_subjects,
  SUM (CASE
    WHEN number_of_lectures <= 20 THEN 1
    ELSE 0
  END) AS elective_subjects
FROM subject
GROUP BY department;

Analizziamo il codice! Per iniziare, si seleziona la colonna dipartimento dalla tabella subject. Ora viene l'uso curioso di SUM() con CASE WHEN. Questa espressione dice che ogni volta che la number_of_lectures è maggiore di 20, alla riga viene assegnato il valore 1. Se la condizione non è soddisfatta, il valore assegnato è 0.

La funzione SUM() somma tutte le righe che hanno il valore assegnato uguale a 1. Pensate un attimo: se sommate tutti gli 1, cosa otterrete? Esattamente, è lo stesso che si otterrebbe se si contassero le righe il cui number_of_lectures è superiore a 20. L'uso di un'espressione CASE WHEN per assegnare i valori 0 o 1 alle righe della tabella è solo un piccolo trucco per far sì che SUM() restituisca il numero di righe proprio come farebbe la funzione COUNT(). Il numero di materie con più di 20 lezioni sarà mostrato nella colonna mandatory_subjects.

La stessa logica si applica alla successiva espressione CASE WHEN. L'unica differenza è che la condizione si riferisce a 20 o meno lezioni, con il risultato mostrato nella colonna elective_subjects.

Infine, il risultato della query viene raggruppato in base alla colonna dipartimento. Ecco il risultato di questa query:

Se avete problemi con GROUP BY, questo articolo che ne spiega la logica può esservi utile. Se invece siete alla ricerca di qualche bell'esempio, ecco un articolo che fornisce cinque esempi di GROUP BY.

Lasciate che vi mostri un altro esempio dell'espressione CASE WHEN. Si dice che la pratica renda perfetti. Beh, non è del tutto vero: la perfezione non esiste! Forse non vi renderà perfetti, ma la pratica vi farà sicuramente migliorare nella scrittura del codice.

Esempio 3: Uso di un'espressione CASE WHEN con SUM() e GROUP BY

In questo ultimo esempio, utilizzerò la tabella orders che contiene le seguenti colonne:

• id: l'ID dell'ordine.
• total_price: il prezzo totale dell'ordine.
• order_date: la data dell'ordine.
• status: lo stato dell'ordine.
• ship_country: il paese in cui l'ordine deve essere spedito.

Il compito è quello di mostrare il numero di ordini spediti per paese. L'ordine si considera spedito se il suo stato è "spedito" o "consegnato". Questo codice consente di ottenere il risultato desiderato:

SELECT 
  ship_country,
  SUM(CASE
    WHEN status = 'shipped' OR status = 'delivered' THEN 1
    ELSE 0
  END) AS order_shipped
FROM orders
GROUP BY ship_country;

Il codice seleziona la colonna ship_country dalla tabella orders. Quindi utilizza un'espressione CASE WHEN per assegnare il valore 1 a tutte le righe con lo stato "spedito" o "consegnato". A tutti gli altri stati sarà assegnato il valore 0. La nuova colonna è denominata order_shipped. Infine, il risultato viene raggruppato dalla colonna ship_country. Ecco il risultato:

Se siete interessati a utilizzare la funzione SUM() in modo più convenzionale, ecco un articolo che vi aiuterà a utilizzarla per la sintesi condizionale.

State iniziando a scoprire le possibilità dell'espressione CASE WHEN?

Spero che questo articolo vi abbia aiutato a capire l'espressione CASE WHEN in generale, spiegandone la logica e mostrandovi esempi di come funziona. L'uso delle espressioni CASE WHEN può permettervi di sfruttare a pieno il potenziale di SQL. Questa espressione è resa ancora più potente se combinata con funzioni aggregate quali SUM(), come ho mostrato nei due esempi soprastanti.

Potete imparare ancora di più nel corso Creating Basic SQL Reports. Potete informarvi sul corso in un episodio della nostra serie " Corso del mese". Per ulteriori esercitazioni su SQL, consultate il nostro SQL Practice. Contiene oltre 500 esercizi di SQL e ne aggiungiamo di nuovi continuamente!