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

 

 



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