Riepiloghi con subtotali
Gli argomenti di questa pagina
- Subtotali
- Codice per eseguire le Query
- Conteggio delle città nella tabella "sql_rubrica"
- Possibili lavori con la tabella sql_libri
- Visualizzare più campi con con SELECT e MAX()
|
Subtotali
Oltre che avere dei riepiloghi generali su una unica riga a livello dell'intera tabella utilizzando le funzioni di aggregazione COUNT, MAX, MIN, AVG. SUM, come è stato visto nella pagina precedente, aggiungendo la clausola GROUP BY è possibile ottenere diverse righe con dei subtotali per ogni campo o gruppo di campi indicati nella stessa clausola.
Aggiungendo il GROUP BY sui campi indicati nella clausola SELECT, il risultato aggregato viene fornito per ciascuna combinazione dei valori presenti nelle colonne elencate nella clausola GROUP BY.
Codice per eseguire le Query
Tutte le Query presentate come esempi sono eseguite da 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);
|
NB: per ragione di spazio se il resultset risulta numeroso verrà visualizzato con minor numero di righe
NB: la clausola COUNT (*) che si vede in certe Query di riepilogo fa riferimento solamente ai campi indicati nella clausola GROUP BY ovvero conterà il numero di record che hanno valori uguali nei campi indicati nella clausola GROUP BY.
Anche se la clausola
GROUP BY provvede ad ordinare il resultset in base alle colonne in essa specificate, a volte si intende ordinare il resultset su una specifica colonna diversa da quelle specificate. In questo caso è lecito usare la clausola ORDER BY.
Conteggio delle città nella tabella "sql_rubrica"
Elenca quanti contatti abitano in ogni città
Indicando il solo campo citta mi viene restituito il numero di record qui sotto indicato
esempio 1
1
2
3
4
5
6
7
|
<?php $Query = "SELECT citta, count( * ) AS tot FROM `sql_rubrica` GROUP BY citta ORDER BY tot DESC "; ?>
|
|
18 record trovati
15 record visualizzati
citta |
tot |
Giusvalla |
19 |
Condofuri |
14 |
Bevagna |
13 |
Agropoli |
12 |
Ville di Fano |
10 |
Castel Vittorio |
10 |
Abbadia Lariana |
8 |
AbanoTerme |
3 |
Varena |
2 |
MASSARO |
2 |
Correzzana |
1 |
Marsiliana |
1 |
Falconara Alta |
1 |
Borgo S. Dalmazzo |
1 |
San Leonardo in Passiria |
1 |
... |
... |
|
Il risultato tuttavia non è attendibile. Sappiamo per certo che alcune città sono, con lo stesso nome, in più province. Quindi la Query precedente deve essere modificata come la seguente dove, nella clausola SELECT e nella clausola GROUP BY aggiungiamo anche il CAP.
Infatti, come si legge dal numero di record ottenuti, questi risultano superiori a quelli letti con la precedente Query perchè le città con lo stesso nome ma con CAP differente perchè appartenenti a province differenti vengono giustamente ripetute nel resultset ottenuto.
esempio 1 - a
1
2
3
4
5
6
7
|
<?php $Query = "SELECT citta, cap, count( * ) AS tot FROM `sql_rubrica` GROUP BY citta, cap ORDER BY tot DESC "; ?>
|
|
22 record trovati
15 record visualizzati
citta |
cap |
tot |
Giusvalla |
26020 |
14 |
Bevagna |
94014 |
13 |
Ville di Fano |
51021 |
10 |
Castel Vittorio |
89844 |
10 |
Condofuri |
95022 |
10 |
Agropoli |
46011 |
9 |
Abbadia Lariana |
67030 |
8 |
Condofuri |
85022 |
4 |
Agropoli |
56011 |
3 |
Giusvalla |
56020 |
3 |
AbanoTerme |
21049 |
3 |
Varena |
35031 |
2 |
MASSARO |
12345 |
2 |
Giusvalla |
36020 |
2 |
Marsiliana |
53021 |
1 |
... |
... |
... |
|
Aumentando il numero di campi nella clausola SELECT e nella clausola GROUP BY ci aspetteremmo un aumento di righe nel resultset. Questo è parzialmente vero, ma non nel nostro caso. Infatti nella seguente Query aumento ancora di uno la combinazione dei campi da conteggiare aggiungendo anche il campo relativo alla provincia.
Infatti l'aggiunta del campo relativo alla provincia non causa l'alterazione del numero di righe perchè per motivi anagrafici un determinato CAP di solito è abbinato alla coppia città - provincia e nella tabella che abbiamo non c'è alcuna città, della stessa provincia, con più CAP e anche se così fosse al nome della città sarebbe abbinata anche una determinata zona (lido, alta, bassa, stazione, ecc) o sarebbe una frazione della città, quindi con un nome differente.
esempio 1 - b
1
2
3
4
5
6
7
|
<?php $Query = "SELECT citta, prov, cap, count( * ) AS tot FROM `sql_rubrica` GROUP BY citta, prov, cap ORDER BY tot DESC "; ?>
|
|
22 record trovati
15 record visualizzati
citta |
prov |
cap |
tot |
Giusvalla |
CA |
26020 |
14 |
Bevagna |
TA |
94014 |
13 |
Ville di Fano |
RF |
51021 |
10 |
Castel Vittorio |
CH |
89844 |
10 |
Condofuri |
DO |
95022 |
10 |
Agropoli |
DE |
46011 |
9 |
Abbadia Lariana |
AB |
67030 |
8 |
Condofuri |
RO |
85022 |
4 |
Agropoli |
GH |
56011 |
3 |
Giusvalla |
DE |
56020 |
3 |
AbanoTerme |
AT |
21049 |
3 |
Varena |
AE |
35031 |
2 |
MASSARO |
BC |
12345 |
2 |
Giusvalla |
LM |
36020 |
2 |
Marsiliana |
AB |
53021 |
1 |
... |
... |
... |
... |
|
Mostra quanti comuni sono in ogni provincia della mia tabella
esempio 2
1
2
3
4
5
6
7
|
<?php $Query = "SELECT prov, count( * ) AS num_citta FROM `sql_citta` GROUP BY prov ORDER BY num_citta DESC "; ?>
|
|
31 record trovati
15 record visualizzati
prov |
num_citta |
NA |
3 |
CN |
2 |
VA |
2 |
RC |
2 |
AN |
2 |
AQ |
2 |
PD |
1 |
LC |
1 |
PC |
1 |
PG |
1 |
IM |
1 |
PR |
1 |
MB |
1 |
SV |
1 |
GR |
1 |
... |
... |
|
Possibili lavori con la tabella sql_libri
Prima di iniziare il lavoro sulla tabella sql_libri vediamo una sintesi integrale di questa tabella.
Mostra la tabella integrale
La tabella integrale
1
2
3
4
5
|
<?php $Query = "SELECT gruppo, Titolo, prezzo, quantita FROM sql_libri "; ?>
|
|
22 record visualizzati
gruppo |
Titolo |
prezzo |
quantita |
macromedia |
Macromedia Freehand 10 |
78.99 |
197 |
macromedia |
Macromedia Flash 6 |
78.99 |
291 |
macromedia |
Macromedia Director Shockwave |
140.00 |
144 |
macromedia |
Macromedia Authorware 6 |
98.99 |
237 |
adobe |
Adobe Photoshop 7.0 |
670.00 |
498 |
macromedia |
Macromedia Homesite |
90.00 |
398 |
macromedia |
Macromedia Sitespring |
120.00 |
219 |
macromedia |
Macromedia Spectra |
240.00 |
36 |
adobe |
Adobe After Effects 5.5 |
160.00 |
5 |
adobe |
Adobe Digital Video Collection |
670.00 |
1 |
adobe |
Adobe GoLive/LiveMotion Pack |
499.99 |
110 |
adobe |
Adobe Illustrator 10 |
179.50 |
48 |
adobe |
Adobe Dimensions 3.0 |
279.99 |
149 |
adobe |
Adobe PageMaker 7.0 |
80.00 |
309 |
corel |
CorelDRAW |
670.00 |
448 |
corel |
Corel |
190.00 |
119 |
corel |
KPT Tools 6 |
42.00 |
559 |
corel |
KPT Vector Effects |
72.00 |
560 |
corel |
Bryce |
112.00 |
692 |
curiouslabs |
Poser 4 Pro |
420.99 |
36 |
scansoft |
Kai`s PhotoSoap2 |
130.00 |
359 |
scansoft |
PhotoShow |
230.00 |
179 |
|
Conoscendo il contenuto di questa tabella iniziamo alcune possibili interrogazioni che possiamo fare su di essa.
Mostra quanti titoli ha ciascun gruppo
esempio 3
1
2
3
4
5
6
7
|
<?php $Query = "SELECT gruppo, count( * ) AS tot FROM sql_libri GROUP BY gruppo ORDER BY tot DESC "; ?>
|
|
5 record trovati
5 record visualizzati
gruppo |
tot |
macromedia |
7 |
adobe |
7 |
corel |
5 |
scansoft |
2 |
curiouslabs |
1 |
|
Per ogni gruppo indica a quanto ammonta il prezzo totale dei libri
esempio 4
1
2
3
4
5
6
7
|
<?php $Query = "SELECT gruppo, SUM(quantita * prezzo) AS tot FROM sql_libri GROUP BY gruppo ORDER BY tot DESC "; ?>
|
|
5 record trovati
5 record visualizzati
gruppo |
tot |
adobe |
465183.41 |
corel |
464072.00 |
macromedia |
152907.75 |
scansoft |
87840.00 |
curiouslabs |
15155.64 |
|
Per ogni titolo di libro disponibile mostra il totale del prezzo
esempio 5
1
2
3
4
5
6
7
8
|
<?php $Query = "SELECT gruppo, Titolo, prezzo, quantita, SUM(quantita * prezzo) AS tot FROM sql_libri GROUP BY Titolo ORDER BY gruppo, tot DESC "; ?>
|
|
22 record trovati
15 record visualizzati
gruppo |
Titolo |
prezzo |
quantita |
tot |
adobe |
Adobe Photoshop 7.0 |
670.00 |
498 |
333660.00 |
adobe |
Adobe GoLive/LiveMotion Pack |
499.99 |
110 |
54998.90 |
adobe |
Adobe Dimensions 3.0 |
279.99 |
149 |
41718.51 |
adobe |
Adobe PageMaker 7.0 |
80.00 |
309 |
24720.00 |
adobe |
Adobe Illustrator 10 |
179.50 |
48 |
8616.00 |
adobe |
Adobe After Effects 5.5 |
160.00 |
5 |
800.00 |
adobe |
Adobe Digital Video Collection |
670.00 |
1 |
670.00 |
corel |
CorelDRAW |
670.00 |
448 |
300160.00 |
corel |
Bryce |
112.00 |
692 |
77504.00 |
corel |
KPT Vector Effects |
72.00 |
560 |
40320.00 |
corel |
KPT Tools 6 |
42.00 |
559 |
23478.00 |
corel |
Corel |
190.00 |
119 |
22610.00 |
curiouslabs |
Poser 4 Pro |
420.99 |
36 |
15155.64 |
macromedia |
Macromedia Homesite |
90.00 |
398 |
35820.00 |
macromedia |
Macromedia Sitespring |
120.00 |
219 |
26280.00 |
... |
... |
... |
... |
... |
|
Per ogni titolo di libro disponibile per i gruppi macromedia e corel mostra il totale del prezzo
Nel caso della seguente Query ho aggiunto la condizione di cercare i libri solo tra determinati gruppi.
esempio 6
1
2
3
4
5
6
7
|
<?php $Query = "SELECT gruppo, Titolo, (quantita * prezzo) AS tot FROM sql_libri WHERE gruppo IN ('macromedia', 'corel') ORDER BY tot DESC "; ?>
|
|
12 record trovati
12 record visualizzati
gruppo |
Titolo |
tot |
corel |
CorelDRAW |
300160.00 |
corel |
Bryce |
77504.00 |
corel |
KPT Vector Effects |
40320.00 |
macromedia |
Macromedia Homesite |
35820.00 |
macromedia |
Macromedia Sitespring |
26280.00 |
corel |
KPT Tools 6 |
23478.00 |
macromedia |
Macromedia Authorware 6 |
23460.63 |
macromedia |
Macromedia Flash 6 |
22986.09 |
corel |
Corel |
22610.00 |
macromedia |
Macromedia Director Shockwave |
20160.00 |
macromedia |
Macromedia Freehand 10 |
15561.03 |
macromedia |
Macromedia Spectra |
8640.00 |
|
Per ogni gruppo indica il prezzo più alto dei libri in esso compresi
esempio 7
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 |
|
Visualizzare più campi con con SELECT e MAX()
Supponiamo di ampliare la richiesta chiedendo di voler visualizzare anche il titolo associato al maggior prezzo
La prima soluzione che viene in mente è questa Query che però non mi restituisce il risultato atteso.
Per ogni gruppo indica il prezzo più alto dei libri in esso compresi ed il libro associato a quel prezzo (soluzione errata)
Un modo errato per risolvere il problema
1
2
3
4
5
6
7
|
<?php $Query = "SELECT gruppo, Titolo, MAX( prezzo ) AS max_prezzo FROM sql_libri GROUP BY gruppo ORDER BY max_prezzo "; ?>
|
|
5 record trovati
5 record visualizzati
gruppo |
Titolo |
max_prezzo |
scansoft |
Kai`s PhotoSoap2 |
230.00 |
macromedia |
Macromedia Freehand 10 |
240.00 |
curiouslabs |
Poser 4 Pro |
420.99 |
adobe |
Adobe Photoshop 7.0 |
670.00 |
corel |
CorelDRAW |
670.00 |
|
Per ottenere il risultato atteso occorre cambiare radicalmente la Query iniziale. Qui di seguito ne presento due che ottengono lo stesso risultato.
Con la prima versione scelgo semplicemente i tre campi interessati mettendo nella WHERE una intera istruzione SELECT che mi restituisce il max valore del libro in esame e scegliendo il solo record che ha il prezzo maggiore.
Con la seconda versione uso un duplicato della tabella sql_libri lavorando con la tabella originale ed il suo duplicato
Per ogni gruppo indica il prezzo più alto dei libri in esso compresi ed il libro associato a quel prezzo (due soluzioni definitive)
Query risolutiva
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
<?php $Query = "SELECT gruppo, Titolo, prezzo FROM sql_libri WHERE prezzo=(SELECT MAX(tab_1.prezzo) FROM sql_libri AS tab_1 WHERE sql_libri.gruppo = tab_1.gruppo ) GROUP BY gruppo, Titolo ";
$Query_1 = "SELECT sql_libri.gruppo, sql_libri.Titolo, sql_libri.prezzo FROM sql_libri LEFT JOIN sql_libri AS tab_1 ON sql_libri.gruppo = tab_1.gruppo AND sql_libri.prezzo < tab_1.prezzo WHERE tab_1.gruppo IS NULL ORDER BY gruppo, Titolo ";
?>
|
|
6 record trovati
6 record visualizzati
gruppo |
Titolo |
prezzo |
adobe |
Adobe Photoshop 7.0 |
670.00 |
macromedia |
Macromedia Spectra |
240.00 |
adobe |
Adobe Digital Video Collection |
670.00 |
corel |
CorelDRAW |
670.00 |
curiouslabs |
Poser 4 Pro |
420.99 |
scansoft |
PhotoShow |
230.00 |
|