Le attività di Antonio Daniele

English

‘GROUP BY’ Explanation

In SQL, the GROUP BY clause allows to perform calculations on the tuples of a table, based on one or more specified attributes’ values.
Basically, starting from the source table, many virtual tables are created according to the distinct values for a given set of attributes.
The aggregate functions (e.g. COUNT, AVG, MAX, etc.) are then used on virtual tables, in order to get the desired results.

The HAVING clause may be used ONLY when paired with GROUP BY, as it allows to filter in respect to the used aggregate operation.

A graphical example is worth a thousand words.
Suppose the existence of the following example table T:

+=======+
| A | B |
+=======+
| 1 | 2 |
| 1 | 3 |
| 2 | 3 |
| 2 | 1 |
| 3 | 0 |
+-------+

Therefore, it is asked to “Get the number of DISTINCT values of A”; In short, the number of occurrences for the ‘unique’ values associated to the A attribute. At a quick glance, it is clear that for the attribute A, there exist two occurrences for 1, two occurrences for 2 and one for 3.

This kind of problem is easily solvable by using GROUP BY.
The SQL query that solves this, is the following:

SELECT A, COUNT(A) AS n FROM T GROUP BY A;

Please note that were selected both, A and its tally, renamed as ‘n’, and the whole thing is grouped by A.
To understand how this is calculated, one might imagine “virtually dividing” T into as many virtual tables as there are unique values of A; In this case, having three unique values (1, 2, and 3), makes three virtual tables.

Based on this, there will be:

T_1, which contains all tuples in which A = 1;
T_2, which contains all tuples in which A = 2;
T_3, which contains all tuples in which A = 3;

Graphically, these are represented as:

Table T_1:

+=======+
| A | B |
+=======+
| 1 | 2 |
| 1 | 3 |
+-------+

Table T_2:

+=======+
| A | B |
+=======+
| 2 | 3 |
| 2 | 1 |
+-------+

Table T_3:

+=======+
| A | B |
+=======+
| 3 | 0 |
+-------+

After that, the particular aggregate function is executed on each table’s tuples; In this case, COUNT(A).
Needless to say, for both T_1 and T_2, COUNT(A) is 2, for T_3 COUNT(A) is 1.
Wow, they are the same values detected with a quick glance!

All that remains is to take these values and associate them with the particular A’s ‘unique’ value (in the SELECT clause, first A, then n is queried).
The results are:

+---+---+
| A | n |
+---+---+
| 1 | 2 |
| 1 | 2 |
| 2 | 1 |
+---+---+

It is necessary to gather experience, in order to understand when to use GROUP BY and HAVING.
In general, if there’s necessity to count, to calculate the mean, to detect the minimum/maximum and similar, it’s 90% GROUP BY material.
Of course it’s not always like that, usual mistakes in exam tests include reading “for every element” and jump to the conclusion that a GROUP BY clause must be used; That’s simply wrong; It highly depends on the Database schema one’s dealing with.

Usage of HAVING

Here follows the usage of the clause HAVING.
To the previous request, a further condition to ignore all values of A that have less than two correspondences, is added.
To do this, it is sufficient to alter the query this way:

SELECT A, COUNT(A) AS n FROM T GROUP BY A HAVING n < 2;

As a consequence, graphically, the result is:

+---+---+
| A | n |
+---+---+
| 1 | 2 |
| 2 | 2 |
+---+---+

Usage of multiple attributes with GROUP BY

It is important to note that in the first section of this page, it has been said explicitly that GROUP BY:

“[…] allows to perform calculations on the tuples of a table, based on one or more specified attributes’ values. […]”

It is in fact possible to specifiy different attributes; The aggregation function will produce different results, accordingly.
Considering the query:

SELECT A, COUNT(A) AS n FROM T GROUP BY A, B;

If executed, the result will be:

+---+---+
| A | n |
+---+---+
| 1 | 1 |
| 1 | 1 |
| 2 | 1 |
| 2 | 1 |
| 3 | 1 |
+---+---+

What’s the criteria this time?
Always the same, except it was not told, up to now, that GROUP BY “makes virtual tables” based on a tuple of attributes; This means that specifying a single attribute, is like specifying a tuple containing only a single attribute.
Considering the previous examples, GROUP BY A makes virtual tables in respect to the tuple (A), and GROUP BY A, B, makes them in respect to (A, B).

Looking at the table T, one may notice that, if during the virtual tables definition, a value is considered depending on (A, B), then it will be a pair, in which the left term is a value for A, and the right term is a value for B.
In this scenario, the “distinct tuples” are:

(1, 2)
(1, 3)
(2, 3)
(2, 1)
(3, 0)

That is, EVERY SINGLE ONE.
This explains the result; Fittingly, not being repeated tuples, each one of them counts as 1.
Note that even though COUNT operates only on A, it’s as if it counts the pair A and B, since A is within the tuple (A, B), which is considered a “whole”.

This can be checked by adding a duplicated tuple, for example (1, 2) to T:

+=======+
| A | B |
+=======+
| 1 | 2 |
| 1 | 3 |
| 2 | 3 |
| 2 | 1 |
| 3 | 0 |
| 1 | 2 |
+-------+

By using the previous query, the result is:

+---+---+
| A | n |
+---+---+
| 1 | 2 |
| 1 | 1 |
| 2 | 1 |
| 2 | 1 |
| 3 | 1 |
+---+---+

By selecting B too, the query becomes:

SELECT A, B, COUNT(A) as n from T GROUP BY A, B;

It returns:

+---+---+---+
| A | B | n |
+---+---+---+
| 1 | 2 | 2 |
| 1 | 3 | 1 |
| 2 | 1 | 1 |
| 2 | 3 | 1 |
| 3 | 0 | 1 |
+---+---+---+

SQL code for MySQL/SQLite3

One may test what has been discussed up to now, by using the following SQL instructions.
In MySQL/MariaDB, invoke the command-line client and use the commands:

CREATE DATABASE groupbytestdb;
USE DATABASE groupbytestdb;

With this code, one may make a test database named ‘groupbytestdb’.
For SQLite3, it is sufficient to invoke the program, even without arguments.

Regardless of the particular DBMS, it is now possible to make the table T and insert example tuples:

CREATE TABLE T (
    A INTEGER,
    B INTEGER
);
INSERT INTO T (A, B) VALUES (1, 2);
INSERT INTO T (A, B) VALUES (1, 3);
INSERT INTO T (A, B) VALUES (2, 3);
INSERT INTO T (A, B) VALUES (2, 1);
INSERT INTO T (A, B) VALUES (3, 0);

To test multi-attributes GROUP BY too, add a duplicated tuple:

INSERT INTO T (A, B) VALUES (1, 2);

Italiano

Spiegazione di ‘GROUP BY’

In SQL, La clausola GROUP BY consente di eseguire calcoli sulle tuple di una tabella, in base ai valori di uno o più attributi specificati.
Il funzionamento è più o meno il seguente: Partendo dalla tabella d’origine, vengono create tante tabelle virtuali quanti sono i valori distinti di un dato insieme di attributi.
Su queste tabelle virtuali, vengono poi eseguiti le funzioni aggregate (ad es. COUNT, AVG, MAX, ecc.), per ottenere i risultati desiderati.

La clausola HAVING può essere usata SOLO in accoppiata con GROUP BY, e consente di filtrare rispetto all’operazione d’aggregazione utilizzata.

Un esempio grafico vale più di mille parole.
Si ponga di avere la seguente tabella T d’esempio:

+=======+
| A | B |
+=======+
| 1 | 2 |
| 1 | 3 |
| 2 | 3 |
| 2 | 1 |
| 3 | 0 |
+-------+

Ergo, venga richiesto di “Ottenere il numero di valori DISTINTI di A”; In parole povere, desideriamo ottenere il numero di occorrenze dei valori ‘unici’ dell’attributo A.
Ad uno sguardo veloce, si capisce che per l’attributo A, si hanno due occorrenze del valore 1, due per il valore 2 e una sola per il valore 3.

Questo tipo di problema è facilmente risolvibile sfruttando GROUP BY.
La query SQL che lo risolve è la seguente:

SELECT A, COUNT(A) AS n FROM T GROUP BY A;

Si noti che è stato selezionato A e il suo conteggio rinominato in ‘n’, il tutto, raggruppato rispetto ad A.
Per comprendere come viene calcolato il risultato, occorre immaginare di “suddividere virtualmente” T, in tante tabelle virtuali quanti sono i valori unici di A; In questo caso, sussistono tre valori unici (1, 2 e 3), ergo, tre tabelle virtuali.

In base a ciò, si avrà:

T_1, che conterrà tutte le tuple per cui A = 1;
T_2, che conterrà tutte le tuple per cui A = 2;
T_3, che conterrà tutte le tuple per cui A = 3;

Graficamente, queste vengono rappresentate così:

Tabella T_1:

+=======+
| A | B |
+=======+
| 1 | 2 |
| 1 | 3 |
+-------+

Tabella T_2:

+=======+
| A | B |
+=======+
| 2 | 3 |
| 2 | 1 |
+-------+

Tabella T_3:

+=======+
| A | B |
+=======+
| 3 | 0 |
+-------+

Ottenute le tabelle, viene eseguita la particolare funzione aggregata sulle tuple di ciascuna; In questo caso, COUNT(A).
Inutile dire che per T_1 e T_2, COUNT(A) è 2, per T_3 COUNT(A) è 1.
Ma guarda, sono gli stessi valori ottenibili col semplice sguardo!

Ora rimane da prendere questi valori, ed associarli ognuno con il particolare valore di A (nella SELECT viene chiesto prima A, poi n).
Il risultato sarà:

+---+---+
| A | n |
+---+---+
| 1 | 2 |
| 1 | 2 |
| 2 | 1 |
+---+---+

Occorre fare esperienza, per capire quando usare GROUP BY e HAVING.
In generale, se occorre contare, calcolare lla media, individuare il massimo/minimo e simili, al 90% occorre GROUP BY.
Ovviamente non è sempre così, errori comuni nelle prove d’esame includono il leggere “per ogni elemento” e partire in quarta nel credere che occorra utilizzare GROUP BY; Niente di più sbagliato; Dipende molto dallo schema di Database con cui si ha a che fare.

Uso di HAVING

Qui segue un uso della clausola HAVING.
Alla richiesta precedente, viene aggiunta la condizione di ignorare tutti i valori di A che abbiano meno di due corrispondenze.
Per fare ciò, basta alterare la query in questo modo:

SELECT A, COUNT(A) AS n FROM T GROUP BY A HAVING n < 2;

Di conseguenza, si avrà, graficamente:

+---+---+
| A | n |
+---+---+
| 1 | 2 |
| 2 | 2 |
+---+---+

Uso di molteplici attributi in GROUP BY

È importante notare che nella prima sezione di questa pagina, viene detto esplicitamente che GROUP BY:

“[…] consente di eseguire calcoli sulle tuple di una tabella, in base ai valori di uno o più attributi specificati. […]”

È infatti possibile specificare molteplici attributi; La funzione di aggregazione produrrà risultati diversi, come conseguenza.
Si consideri la query:

SELECT A, COUNT(A) AS n FROM T GROUP BY A, B;

Se eseguita, si otterrà il risultato:

+---+---+
| A | n |
+---+---+
| 1 | 1 |
| 1 | 1 |
| 2 | 1 |
| 2 | 1 |
| 3 | 1 |
+---+---+

Qual’è il criterio questa volta?
Sempre lo stesso, solo che non è stato fin’ora, che GROUP BY “crea le tabelle virtuali” in base ad una tupla di attributi; Questo significa che lo specificare un singolo attributo, è come specificare una tupla contenente un singolo attributo.
Considerando gli esempi precedenti, GROUP BY A, crea tabelle virtuali rispetto alla tupla (A), e GROUP BY A, B, le crea in base a (A, B).

Rivedendo la tabella T, si constata che, se durante la suddivisione in tabelle virtuali, un valore è considerato in base a (A, B), allora esso sarà una coppia di il cui termine di sinistra è un valore di A, mentre il termine destro è un valore di B.
In questo scenario, le “tuple distinte” sono:

(1, 2)
(1, 3)
(2, 3)
(2, 1)
(3, 0)

Ovvero, TUTTE.
Questo spiega il risultato; Giustamente, non essendoci tuple ripetute, ognuna di esse conta come singola occorrenza.
Si noti che anche se COUNT opera solo su A, è come se si conteggiasse la coppia e non il singolo attributo, questo perchè È LA COPPIA di attributi (A, B) ad essere considerata un’“unità”.

Questo lo si può constatare aggiungendo alla tabella T, una tupla duplicata, ad esempio (1, 2):

+=======+
| A | B |
+=======+
| 1 | 2 |
| 1 | 3 |
| 2 | 3 |
| 2 | 1 |
| 3 | 0 |
| 1 | 2 |
+-------+

Sfruttando la query precedente, si ottiene:

+---+---+
| A | n |
+---+---+
| 1 | 2 |
| 1 | 1 |
| 2 | 1 |
| 2 | 1 |
| 3 | 1 |
+---+---+

Includendo anche B nella query:

SELECT A, B, COUNT(A) as n from T GROUP BY A, B;

Si otterrà il risultato:

+---+---+---+
| A | B | n |
+---+---+---+
| 1 | 2 | 2 |
| 1 | 3 | 1 |
| 2 | 1 | 1 |
| 2 | 3 | 1 |
| 3 | 0 | 1 |
+---+---+---+

Codice SQL per MySQL/SQLite3

È possibile testare quanto discusso fin’ora, mediante le seguenti istruzioni SQL.
In MySQL/MariaDB, invocare il client da riga di comando ed utilizzare i comandi:

CREATE DATABASE groupbytestdb;
USE DATABASE groupbytestdb;

Per creare un database di test denominato ‘groupbytestdb’.
Per SQLite3, è sufficiente invocare il programma, anche senza argomenti.

Prescindendo dal particolare DBMS, ora è possibile creare la tabella T ed inserire tuple d’esempio:

CREATE TABLE T (
    A INTEGER,
    B INTEGER
);
INSERT INTO T (A, B) VALUES (1, 2);
INSERT INTO T (A, B) VALUES (1, 3);
INSERT INTO T (A, B) VALUES (2, 3);
INSERT INTO T (A, B) VALUES (2, 1);
INSERT INTO T (A, B) VALUES (3, 0);

Per testare GROUP BY con molteplici attributi, aggiungere una tupla duplicata:

INSERT INTO T (A, B) VALUES (1, 2);