Raggruppare con GROUP BY e filtrare con HAVING

Gli argomenti di questa pagina

  • Le clausole GROUP BY e HAVING
  • Le tabelle usate in questa pagina
  • Codice per eseguire le Query
  • Esempi di Query con la sola clausola GROUP BY
  • Alcuni esempi con le clausole GROUP BY e HAVING
  • Uso delle clausole WHERE e HAVING

Le clausole GROUP BY e HAVING

Con le query SQL è possibile combinare la sintassi "GROUP BY" con "HAVING" per filtrare ulteriormente i risultati di valori aggregati.
La clausola HAVING specifica ulteriori filtri che vengono applicati dopo i filtri della clausola WHERE.
La condizione di ricerca specificata nella clausola WHERE viene applicata prima dell'operazione di raggruppamento eseguita tramite la clausola GROUP BY, mentre la condizione di ricerca della clausola HAVING viene applicata dopo tale operazione.

La differenza tra le clausole WHERE ed HAVING fondamentalmente sono queste:
con la clausola WHERE si applica la condizione di ogni singola riga.
con la clausola HAVING si applica la condizione di ciascun gruppo di righe ottenute tramite la clausola GROUP BY.
Quando si utilizza con la clausola GROUP BY, è possibile applicare una condizione di filtro per le colonne che appaiono nella clausola GROUP BY.
Se la clausola GROUP BY viene omessa si comporta come la clausola WHERE.

Inizialmente è possiblie rimanere confusi circa la differenza tra il WHERE e HAVING. In fondo con esse si esprimono le stesse condizioni ed anno anche un aspetto sintattico simile.

In pratica:

  • nella clausola SELECT vengono selezionati i campi da visualizzare
  • la clausola FROM definisce la tabella o le tabelle da cui prelevare i dati
  • tramite la clausola WHERE vengono filtrati i record da accettare secondo i criteri espressi
  • con la clausola GROUP BY, i record accettati vengono raggruppati e aggregati
  • la clausola HAVING consente di applicare un ulteriore filtro sui record restituiti dopo il raggruppamento

In questa pagina viene esaminato il modo di restituire le righe in base a un criterio utilizzando HAVING come criterio per l'ulteriore filtro.

Le tabelle usate in questa pagina

Prima di iniziare coi nostri esempi vediamo alcune righe delle tabelle su cui andremo a lavorare

Le due tabelle
Per la tabella sql_libri: 22 record trovati
15 record visualizzati
id gruppo codice Titolo prezzo quantita
1 macromedia MC004 Macromedia Freehand 10 78.99 197
2 macromedia MC003 Macromedia Flash 6 78.99 291
3 macromedia MC002 Macromedia Director Shockwave 140.00 144
4 macromedia MC001 Macromedia Authorware 6 98.99 237
5 adobe AD001 Adobe Photoshop 7.0 670.00 498
6 macromedia MC005 Macromedia Homesite 90.00 398
7 macromedia MC006 Macromedia Sitespring 120.00 219
8 macromedia MC007 Macromedia Spectra 240.00 36
9 adobe AD002 Adobe After Effects 5.5 160.00 5
10 adobe AD003 Adobe Digital Video Collection 670.00 1
11 adobe AD004 Adobe GoLive/LiveMotion Pack 499.99 110
12 adobe AD005 Adobe Illustrator 10 179.50 48
13 adobe AD006 Adobe Dimensions 3.0 279.99 149
14 adobe AD007 Adobe PageMaker 7.0 80.00 309
15 corel CR001 CorelDRAW 670.00 448
... ... ... ... ... ...
Per la tabella sql_rubrica: 101 record trovati
15 record visualizzati
id codice cognome nome indirizzo cap citta prov data_nascita telefono
1 201_parente_000 Migliavacca Luigi VIA P. FIMIANI TRIVIO 35031 Varena AE 2004-01-22 084 41655
2 002_fam_010 Rizzi Carlo VIA SABIN, 34/2 35031 Varena AE 2007-11-22 07 9940508
3 002_fam_020 Liberali Franca VIA DEL TUSCOLANO, 1 67030 Abbadia Lariana AB 2004-02-20 036 348719
4 003_fam_001 Di Maggio Mario TERMINAL CONTENITORI MOLO VII 51021 Ville di Fano RF 2009-05-21 051 18752
5 003_fam_002 Scicchitano Andrea VIA SABIN, 34/2 51021 Ville di Fano RF 1997-01-12 07 74891
6 003_fam_010 Edelvisi Maurizio VIA MATTEO RICCI, 12 51021 Ville di Fano RF 1987-07-02 079 89428
7 003_fam_020 Gerardini Silvano Z.I SAMBUCETO 51021 Ville di Fano RF 2009-08-12 012 164848
8 500_conosc_000 Pasotti Alessio VIA SABIN, 34/2 22050 Correzzana FS 1987-11-14 05 9552713
9 004_fam_001 Calvi Giovanni VIA SABIN, 34/2 53021 Marsiliana AB 1987-07-26 044 5602773
10 004_fam_002 Poggi Giuseppe VIA SABIN, 34/2 67030 Abbadia Lariana AB 1987-01-25 08 0640120
11 004_fam_010 Curti Oreste STRADA MARCHESANE, 43_4 51021 Ville di Fano RF 1988-06-26 027 42781
12 004_fam_021 Borsotti Mirella VIA DEL TUSCOLANO, 1 51021 Ville di Fano RF 1987-07-19 09 379786
13 004_fam_020 Franchinotti Anna VIA BREDINA, 6 67030 Abbadia Lariana AB 2009-05-10 062 8880
14 500_conosc_000 Pischedda Giovanni VIA MATTEO RICCI, 12 22059 Falconara Alta FS 1999-08-08 07 628585
15 004_fam_030 Maini Marilena MACRON TICHON STREET, 27 57020 Borgo S. Dalmazzo BO 2002-03-20 02 6638
... ... ... ... ... ... ... ... ... ...

Codice per eseguire le Query

Tutte le Query presentate come esempi sono eseguibili tramite questo script.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 		<?php
include ('res/db_login.php');
if (!$link = mysql_connect($Host, $User, $Password)) {
    echo mysql_error() . "<br />\n"; 
} else {
    if (!mysql_select_db($DBName)) {
        echo mysql_error() . "<br />\n"; 
    } else {
        mysql_set_charset("utf8");
        if ($result = mysql_query($Query)) {
            $num_row = mysql_num_rows($result);
            $numfields = mysql_num_fields($result);
            $fields = array();
            for ($i=0; $i < $numfields; $i++) {
                $fields[] = mysql_field_name($result, $i);
            }
            if ($num_row > 0) {
                echo $num_row . " record letti<br />\n";
                echo "<table class=\"tabella_it\">\n";        
                echo "<tr>\n";
                for ($i=0; $i < $numfields; $i++) {
                    echo "<th>" . $fields[$i] . "</th>\n";
                }
                echo "</tr>\n";
                while ($row = mysql_fetch_array($result)) {
                    echo "<tr>\n";
                    for ($i=0; $i < $numfields; $i++) {
                        echo "<td>";
                        echo is_null($row[$fields[$i]]) ? 'NULL' : $row[$fields[$i]];
                        echo "</td>\n";
                    }
                    echo "</tr>\n";
                }
                echo "</table>\n";
            } else {
                echo "Nessun record restituito<br />\n";
            }
        } else {
            echo mysql_error() . "<br />\n"; 
        }
    }
}
mysql_close ($link);

Esempi di Query con la sola clausola GROUP BY

Per ogni esempio mostriamo la richiesta che si fa tramite la Query.
Con questo esempio viene cercato il prezzo maggiore tra i libri di ciascun gruppo senza applicare alcun filtro.

Mostra il prezzo più alto dei libri appartenenti a ciascun gruppo

esempio 1
1
2
3
4
5
6
7
 		<?php
$Query = "SELECT gruppo, MAX( prezzo ) AS max_prezzo
FROM sql_libri
GROUP BY gruppo
ORDER BY max_prezzo 
";
?>
5 record trovati
5 record visualizzati
gruppo max_prezzo
scansoft 230.00
macromedia 240.00
curiouslabs 420.99
adobe 670.00
corel 670.00

Questo secondo esempio è simile a quello precedente, ma viene scelta la tabella sql_rubrica e, per ciascuna città vengono contate tutte le occorrenze.
Il count con l'asterisco tra parentesi fa riferimento ai campi indicati nella clausola group by.
La seguente query, anche questa senza alcun filtro, pone la seguente richiesta:

Mostra i cap, le città e provinve e il numero delle occorrenze delle città

esempio 1 a
1
2
3
4
5
6
7
 		<?php
$Query = "SELECT cap, citta, prov, COUNT(*) AS num_citta
FROM sql_rubrica 
GROUP BY citta 
ORDER BY num_citta 
";
?>
18 record trovati
15 record visualizzati
cap citta prov num_citta
84042 Corinzio DO 1
22050 Correzzana FS 1
53021 Marsiliana AB 1
22059 Falconara Alta FS 1
57020 Borgo S. Dalmazzo BO 1
51023 San Leonardo in Passiria RF 1
73020 Cereseto BO 1
71021 Coccanile BO 1
12345 MASSARO BC 2
35031 Varena AE 2
21049 AbanoTerme AT 3
67030 Abbadia Lariana AB 8
89844 Castel Vittorio CH 10
51021 Ville di Fano RF 10
46011 Agropoli DE 12
... ... ... ...

Questo esempio è simile al precedente, ma non esegue il raggruppamento sul solo campo città, ma anche sul campo CAP.
Per questo motivo il resultset restituito è più lungo del precedente.
La Query pone questo quesito:

Mostra i cap, le città e provinve e il numero delle coppie citta - province

esempio 1 b
1
2
3
4
5
6
7
 		<?php
$Query = "SELECT cap, citta, prov, COUNT(*) AS num_citta
FROM sql_rubrica 
GROUP BY citta, prov
ORDER BY num_citta 
";
?>
22 record trovati
15 record visualizzati
cap citta prov num_citta
22050 Correzzana FS 1
53021 Marsiliana AB 1
22059 Falconara Alta FS 1
57020 Borgo S. Dalmazzo BO 1
51023 San Leonardo in Passiria RF 1
73020 Cereseto BO 1
71021 Coccanile BO 1
84042 Corinzio DO 1
35031 Varena AE 2
12345 MASSARO BC 2
36020 Giusvalla LM 2
21049 AbanoTerme AT 3
56011 Agropoli GH 3
56020 Giusvalla DE 3
85022 Condofuri RO 4
... ... ... ...

Alcuni esempi con le clausole GROUP BY e HAVING

Cominciamo ad applicare un filtro sui gruppi.
Per applicare un filtro su dati raggruppati viene usata la clausola HAVING dopo il raggruppamento.

Cerca i prezzi massimi dei libri appartenenti a ciascun gruppo ma mostra solo quelli che hanno un prezzo maggiore o uguale a 260.00

esempio 2
1
2
3
4
5
6
7
8
 		<?php
$Query = "SELECT gruppo, MAX( prezzo ) AS max_prezzo
FROM sql_libri
GROUP BY gruppo
HAVING max_prezzo >= 260.00 
ORDER BY max_prezzo
";
?>
3 record trovati
3 record visualizzati
gruppo max_prezzo
curiouslabs 420.99
adobe 670.00
corel 670.00

Il problema è simile a quello precedente, ma viene usata la tabella sql_rubrica.

Per ogni provincia mostra quelle che hanno più di 9 città

esempio 3
1
2
3
4
5
6
7
 		<?php
$Query = "SELECT citta, prov, count( citta ) AS tot
FROM sql_rubrica
GROUP BY citta, prov
HAVING tot >= 10
";
?>
5 record trovati
5 record visualizzati
citta prov tot
Ville di Fano RF 10
Bevagna TA 13
Castel Vittorio CH 10
Condofuri DO 10
Giusvalla CA 14

La richiesta è opposta a quella precedente

Per ogni provincia mostra quelle che hanno meno 10 di città

esempio 4
1
2
3
4
5
6
7
 		<?php
$Query = "SELECT citta, prov, count( citta ) AS tot
FROM sql_rubrica
GROUP BY citta, prov
HAVING tot < 10
";
?>
17 record trovati
15 record visualizzati
citta prov tot
Varena AE 2
Abbadia Lariana AB 8
Correzzana FS 1
Marsiliana AB 1
Falconara Alta FS 1
Borgo S. Dalmazzo BO 1
San Leonardo in Passiria RF 1
Cereseto BO 1
Coccanile BO 1
AbanoTerme AT 3
Condofuri RO 4
Corinzio DO 1
Giusvalla LM 2
Giusvalla DE 3
Agropoli DE 9
... ... ...

Qui di seguito vengono mostrate tre Query simili tra loro.
Nella prima viene eseguito il raggruppamento sulla sola provincia
Nella seconda il raggruppamento viene eseguito sulla coppia città e provincia
La terza è simile alla seconda ma non viene eseguito alcun conteggio

Questa Query risponde alla domanda:

Mostra l'elenco delle città e quante città sono archiviate

esempio 5
1
2
3
4
5
6
7
 		<?php
$Query = "SELECT cap, citta, prov, COUNT(*) AS num_citta
FROM sql_rubrica
GROUP BY citta, prov
HAVING COUNT(*) > 1 
";
?>
14 record trovati
14 record visualizzati
cap citta prov num_citta
35031 Varena AE 2
67030 Abbadia Lariana AB 8
51021 Ville di Fano RF 10
21049 AbanoTerme AT 3
94014 Bevagna TA 13
89844 Castel Vittorio CH 10
95022 Condofuri DO 10
85022 Condofuri RO 4
26020 Giusvalla CA 14
36020 Giusvalla LM 2
56020 Giusvalla DE 3
46011 Agropoli DE 9
56011 Agropoli GH 3
12345 MASSARO BC 2

In questo esempio il raggruppamento avviene su due campi: città e provincia.
Quindi questa Query risponde alla domanda:

Mostra l'elenco delle città, anche se di diverse province, e quante città sono archiviate

esempio 5 a
1
2
3
4
5
6
7
8
 		<?php
$Query = "SELECT cap, citta, prov, COUNT(*) AS num_citta
FROM sql_rubrica
GROUP BY citta, prov
HAVING COUNT(*) > 1 
ORDER BY num_citta DESC
";
?>
14 record trovati
14 record visualizzati
cap citta prov num_citta
26020 Giusvalla CA 14
94014 Bevagna TA 13
51021 Ville di Fano RF 10
89844 Castel Vittorio CH 10
95022 Condofuri DO 10
46011 Agropoli DE 9
67030 Abbadia Lariana AB 8
85022 Condofuri RO 4
21049 AbanoTerme AT 3
56020 Giusvalla DE 3
56011 Agropoli GH 3
35031 Varena AE 2
36020 Giusvalla LM 2
12345 MASSARO BC 2

In questo esempio non viene eseguito nessun conteggio, ma il raggruppamento viene eseguito su città e provincia. La richiesta è la seguente:

Mostra l'elenco delle città e province in cui abitano i nostri contatti

esempio 5 b
1
2
3
4
5
6
7
8
 		<?php
$Query = "SELECT cap, citta, prov
FROM sql_rubrica
GROUP BY citta, prov
HAVING COUNT(*) > 1 
ORDER BY COUNT(*) DESC
";
?>
14 record trovati
14 record visualizzati
cap citta prov
26020 Giusvalla CA
94014 Bevagna TA
51021 Ville di Fano RF
89844 Castel Vittorio CH
95022 Condofuri DO
46011 Agropoli DE
67030 Abbadia Lariana AB
85022 Condofuri RO
21049 AbanoTerme AT
56020 Giusvalla DE
56011 Agropoli GH
35031 Varena AE
36020 Giusvalla LM
12345 MASSARO BC

In questo esempio non viene usato alcun filtro

Mostra il valore complessivo di tutti i libri per ciascun gruppo

esempio 6
1
2
3
4
5
6
7
 		<?php
$Query = "SELECT gruppo, SUM( prezzo ) * SUM( quantita ) AS tot_valore
FROM sql_libri
GROUP BY gruppo
ORDER BY tot_valore
";
?>
5 record trovati
5 record visualizzati
gruppo tot_valore
curiouslabs 15155.64
scansoft 193680.00
macromedia 1289088.34
corel 2582508.00
adobe 2844217.60

Questo esempio è simile al precedente ma come condizione viene posto di trovare solo quei gruppi che abbiano un valore inferiore a 195000

Mostra il valore complessivo di tutti i libri per ciascun gruppo ma che abbiano un valore minore a 195000

esempio 7
1
2
3
4
5
6
7
8
 		<?php
$Query = "SELECT gruppo, SUM( prezzo ) * SUM( quantita ) AS tot_valore
FROM sql_libri
GROUP BY gruppo
HAVING tot_valore <195000
ORDER BY tot_valore
";
?>
2 record trovati
2 record visualizzati
gruppo tot_valore
curiouslabs 15155.64
scansoft 193680.00

Nell'esempio che segue vengono cercati tutti i dati statistici. La richiesta è:

Per ogni gruppo cerca il numeri dei libri, il loro valore massimo, il loro valore minimo, la media del loro prezzo, ed il loro totale

esempio 8
1
2
3
4
5
6
7
8
9
10
11
 		<?php
$Query = "SELECT gruppo, 
COUNT(Titolo) AS num_libri, 
MAX(prezzo) AS max, 
MIN(prezzo) AS min, 
AVG(prezzo) AS avg, 
SUM(prezzo) AS sum 
FROM sql_libri 
GROUP BY gruppo 
";
?>
5 record trovati
5 record visualizzati
gruppo num_libri max min avg sum
macromedia 7 240.00 78.99 120.995714 846.97
adobe 7 670.00 80.00 362.782857 2539.48
corel 5 670.00 42.00 217.200000 1086.00
curiouslabs 1 420.99 420.99 420.990000 420.99
scansoft 2 230.00 130.00 180.000000 360.00

Uso delle clausole WHERE e HAVING

La clausola WHERE e la clausola HAVING sono simili ma:

  • la clausola WHERE è applicabile a righe singole,
  • la clausola HAVING è applicabile solo ai gruppi come insieme, ossia alle righe nel resultset di risultati che rappresentano i gruppi.

Una Query può contenere sia una clausola WHERE che una clausola HAVING ma con queste semplici regole:

  • La clausola WHERE viene applicata prima alle singole righe nelle tabelle o negli oggetti con valori di tabella.
  • Quindi solo le righe che soddisfano le condizioni della clausola WHERE vengono raggruppate.
  • Solo dopo l'operazione di raggruppamento può essere applicata la clausola HAVING alle righe del resultset di risultati. Nell'output della query saranno visualizzati solo i gruppi che soddisfano le condizioni HAVING.

La clausola HAVING può essere applicata solo alle colonne presenti anche nella clausola GROUP BY o in una funzione di aggregazione.

Si usa WHERE per esprimere condizioni su semplici valori, mentre è necessario usare HAVING per esprimere condizioni su valori aggregati.
Quindi la clausola WHERE viene applicata prima di effettuare i raggruppamenti su cui agisce invece la preposizione HAVING. Per questo motivo nella clausola WHERE non si può usare un campo usato in uba funzione di aggregazione.
Quando viene eseguita la Query, vengono prima analizzi i record secondo i criteri della clausola WHERE, poi, con i record risultanti, crea i raggruppamenti con la clausola GROUP BY. A questo punto vengono applicati i criteri specificati nella clausola HAVING.
In questo modo è possibile stabilire la prima condizione includendo la clausola WHERE che consente di eliminare i record non richiesti.
Per la seconda condizione è necessario utilizzare la clausola HAVING, in quanto la condizione si basa sui risultati del raggruppamento e del riepilogo dei dati.

Alla Query vista qui sopra vorremmo aggiungere un filtro per escludere i libri che appartengono al gruppo della Corel. Perciò proviamo a rispondere a questo quesito:

Per ogni gruppo cerca il numeri dei libri, il loro valore massimo, il loro valore minimo, la media del loro prezzo, ed il loro totale ma escludi quelli che hanno un prezzo superiore a 250.00 e che non appartengono al gruppo corel

Viene spontaneo pensare alla seguente Query che purtroppo è destinata a fallire restituendo un errore.
Anche se nella tabella esiste il campo prezzo non viene riconosciuto nella clausola HAVING perchè questo campo non viene elencato nella clausola SELECT della Query. E' questo un caso in cui è necessario ricorrere alla clausola WHERE come mostrato più in basso.

esempio 8 a
Questa Query è errata ... restituisce un errore
1
2
3
4
5
6
7
8
9
10
11
12
 		<?php
$Query = "SELECT gruppo, 
COUNT(Titolo) AS num_libri, 
MAX(prezzo) AS max, 
MIN(prezzo) AS min, 
AVG(prezzo) AS avg, 
SUM(prezzo) AS sum 
FROM sql_libri 
GROUP BY gruppo 
HAVING gruppo <> 'corel' AND prezzo > 250.00 
";
?>
Unknown column 'prezzo' in 'having clause'

Ecco come modificare la Query appena vista.

esempio 8 b
1
2
3
4
5
6
7
8
9
10
11
12
13
 		<?php
$Query = "SELECT gruppo, 
COUNT(Titolo) AS num_libri,
MAX(prezzo) AS max, 
MIN(prezzo) AS min, 
AVG(prezzo) AS avg, 
SUM(prezzo) AS sum 
FROM sql_libri 
WHERE prezzo > 250.00 
GROUP BY gruppo
HAVING gruppo <> 'corel' 
";
?>
2 record trovati
2 record visualizzati
gruppo num_libri max min avg sum
adobe 4 670.00 279.99 529.995000 2119.98
curiouslabs 1 420.99 420.99 420.990000 420.99

Questo esempio lo abbiamo visto nell'esempio 1 b, ma qui includiamo un filtro doppio: uno sulla città ed uno sul numero totale.

Mostra i cap, le città e provinve e il numero delle coppie citta - province se la città è diversa da Giusvalla e se il numero delle coppie citta - province è maggiore di 1

esempio 9
1
2
3
4
5
6
7
 		<?php
$Query = "SELECT cap, citta, prov, COUNT( * ) AS tot
FROM sql_rubrica
GROUP BY citta, prov
HAVING citta <> 'Giusvalla' AND tot > 1 
";
?>
11 record trovati
11 record visualizzati
cap citta prov tot
35031 Varena AE 2
67030 Abbadia Lariana AB 8
51021 Ville di Fano RF 10
21049 AbanoTerme AT 3
94014 Bevagna TA 13
89844 Castel Vittorio CH 10
95022 Condofuri DO 10
85022 Condofuri RO 4
46011 Agropoli DE 9
56011 Agropoli GH 3
12345 MASSARO BC 2

Il seguente esempio mostra un modo alternativo per creare una Query per rispondere al quesito appena vista.
Il filtro è impostato in maniera differente: una delle condizioni è posta nella clausola WHERE ed una nella clausola HAVING.

esempio 9 a
1
2
3
4
5
6
7
8
 		<?php
$Query = "SELECT cap, citta, prov, COUNT( * ) AS tot
FROM sql_rubrica
WHERE citta <> 'Giusvalla'
GROUP BY citta, prov
HAVING tot > 1 
";
?>
11 record trovati
11 record visualizzati
cap citta prov tot
35031 Varena AE 2
67030 Abbadia Lariana AB 8
51021 Ville di Fano RF 10
21049 AbanoTerme AT 3
94014 Bevagna TA 13
89844 Castel Vittorio CH 10
95022 Condofuri DO 10
85022 Condofuri RO 4
46011 Agropoli DE 9
56011 Agropoli GH 3
12345 MASSARO BC 2

 

 



settore tecnico il sito di lorettabweb il Forum di sostegno
il forum il forum il forum