Funzioni di aggregazione SQL in SQLite: COUNT, SUM, AVG, MIN, MAX

Cosa fa davvero una funzione di aggregazione

La maggior parte delle funzioni SQL che hai visto finora lavora riga per riga: UPPER(name) viene eseguita una volta per riga, ROUND(price, 2) anche. Le funzioni di aggregazione sono diverse: guardano un intero insieme di righe e lo riducono a un solo valore.

Prepara una piccola tabella per fare qualche prova:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL,
    status TEXT
);

INSERT INTO orders (customer, amount, status) VALUES
    ('Ada',   42.00, 'paid'),
    ('Boris', 15.50, 'paid'),
    ('Ada',   88.00, 'paid'),
    ('Carl',  10.00, 'refunded'),
    ('Ada',   NULL,  'pending');

SELECT COUNT(*), SUM(amount), AVG(amount) FROM orders;

Entrano cinque righe, ne esce una. Tutto il modello mentale è qui: le aggregazioni schiacciano le righe in un riepilogo. Senza un GROUP BY, il riepilogo copre tutte le righe del risultato.

COUNT: righe contro valori

COUNT ha tre forme, e la differenza conta:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   42.00),
    ('Boris', 15.50),
    ('Ada',   88.00),
    ('Carl',  NULL),
    ('Ada',   NULL);

SELECT
    COUNT(*)               AS rows_total,
    COUNT(amount)          AS rows_with_amount,
    COUNT(DISTINCT customer) AS unique_customers
FROM orders;

• COUNT(*) conta le righe. NULL compresi. Restituisce sempre un numero.
• COUNT(column) conta i valori non NULL di quella colonna.
• COUNT(DISTINCT column) conta i valori unici non NULL.

Cinque righe, tre con un amount, tre clienti distinti. Se ti capita di vedere COUNT(amount) e ti chiedi perché è più piccolo di COUNT(*), ecco il motivo: i NULL non vengono contati.

SUM, AVG, MIN, MAX

Le aggregazioni aritmetiche funzionano come ti aspetti, con una regola silenziosa: saltano tutte i NULL:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (10), (20), (30), (NULL);

SELECT
    SUM(amount) AS total,
    AVG(amount) AS mean,
    MIN(amount) AS smallest,
    MAX(amount) AS largest,
    COUNT(amount) AS counted
FROM orders;

AVG vale (10 + 20 + 30) / 3 = 20.0, non 60 / 4 = 15.0. Il denominatore è il numero di valori non NULL. Se non è quello che vuoi, per esempio se preferisci trattare i dati mancanti come zero, dillo in modo esplicito:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (10), (20), (30), (NULL);

SELECT AVG(COALESCE(amount, 0)) FROM orders;

MIN e MAX funzionano anche su testo e date: confrontano il testo in ordine lessicografico e le date come stringhe ISO, se usi il formato di data standard.

SUM contro TOTAL

SQLite ha una seconda aggregazione simile alla somma, TOTAL, che risolve due fastidi di SUM:

CREATE TABLE empty_table (amount REAL);

SELECT SUM(amount), TOTAL(amount) FROM empty_table;

• SUM su zero righe restituisce NULL. TOTAL restituisce 0.0.
• SUM su valori tutti NULL restituisce NULL. TOTAL restituisce 0.0.
• TOTAL restituisce sempre un numero in virgola mobile, quindi non va mai in overflow come l'aritmetica intera.

Il compromesso: TOTAL non è standard, e il risultato sempre REAL può sorprenderti se ti aspettavi un intero. Usalo quando "nessuna riga significa zero" è la risposta giusta per la tua app, e resta su SUM quando vuoi il comportamento standard di SQL.

DISTINCT dentro le aggregazioni

DISTINCT può stare dentro qualsiasi aggregazione, non solo COUNT. Rimuove i valori duplicati prima che l'aggregazione venga eseguita:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada',   42.00),
    ('Ada',   42.00),
    ('Boris', 15.50),
    ('Carl',  10.00);

SELECT
    SUM(amount)          AS sum_all,
    SUM(DISTINCT amount) AS sum_unique,
    COUNT(DISTINCT customer) AS unique_customers
FROM orders;

SUM(amount) somma l'importo di ogni riga. SUM(DISTINCT amount) somma ogni importo unico una sola volta: utile per cose come "totale degli importi unici delle fatture", ma raramente è quello che ti serve. COUNT(DISTINCT customer) è il caso più comune.

FILTER: aggregare un sottoinsieme

Quando vuoi aggregare solo alcune righe, la mossa ovvia è WHERE. Ma WHERE filtra tutto: così non puoi combinare "conta gli ordini pagati" e "conta i rimborsi" nella stessa query. FILTER risolve il problema:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, status TEXT);
INSERT INTO orders (amount, status) VALUES
    (42.00, 'paid'),
    (15.50, 'paid'),
    (88.00, 'paid'),
    (10.00, 'refunded'),
    (25.00, 'pending');

SELECT
    COUNT(*) FILTER (WHERE status = 'paid')      AS paid_count,
    COUNT(*) FILTER (WHERE status = 'refunded')  AS refund_count,
    SUM(amount) FILTER (WHERE status = 'paid')   AS paid_revenue
FROM orders;

Ogni clausola FILTER (WHERE ...) si applica solo a quella singola aggregazione. Un solo passaggio sulla tabella, più porzioni riassunte. Prima che esistesse FILTER, si scriveva SUM(CASE WHEN status = 'paid' THEN amount END): stessa idea, più cose da scrivere.

GROUP_CONCAT: unire stringhe

GROUP_CONCAT è quella fuori dal coro. Invece di restituire un numero, concatena i valori in un'unica stringa:

CREATE TABLE tags (post_id INTEGER, tag TEXT);
INSERT INTO tags VALUES
    (1, 'sqlite'),
    (1, 'database'),
    (1, 'tutorial'),
    (2, 'python'),
    (2, 'sqlite');

SELECT
    post_id,
    GROUP_CONCAT(tag)        AS default_separator,
    GROUP_CONCAT(tag, ' | ') AS pipe_separated
FROM tags
GROUP BY post_id;

Il separatore predefinito è la virgola. Passa un secondo argomento per usarne un altro. L'ordine non è garantito, a meno che tu non scriva la chiamata come GROUP_CONCAT(tag ORDER BY tag): comodo quando l'output finisce in un'interfaccia e vuoi che resti stabile.

Aggregare senza GROUP BY

Tutti gli esempi finora che usavano aggregazioni senza GROUP BY hanno prodotto esattamente una riga. È questa la regola: una SELECT con aggregazioni e senza GROUP BY è un riepilogo di una sola riga dell'intera tabella (dopo il WHERE).

Puoi combinare le aggregazioni liberamente:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, status TEXT);
INSERT INTO orders (amount, status) VALUES
    (42.00, 'paid'), (15.50, 'paid'), (88.00, 'paid'),
    (10.00, 'refunded'), (25.00, 'pending');

SELECT
    COUNT(*)    AS orders,
    SUM(amount) AS revenue,
    AVG(amount) AS average,
    MIN(amount) AS smallest,
    MAX(amount) AS largest
FROM orders
WHERE status = 'paid';

Quello che non puoi fare è mescolare colonne non aggregate con le aggregazioni e aspettarti risultati sensati:

-- Consentito da SQLite, ma il valore di `customer` è arbitrario.
SELECT customer, SUM(amount) FROM orders;

Qui SQLite non dà errore (altri database sì), ma sceglierà il nome di un cliente a caso da mostrare accanto al totale. Se vuoi una somma per cliente, ti serve GROUP BY, che è l'argomento della prossima pagina.

Prossimo passo: GROUP BY e HAVING

Le aggregazioni sull'intera tabella rispondono a "quanto in totale". Le aggregazioni per gruppo, per cliente, per mese, per stato, rispondono alle domande più interessanti. GROUP BY serve a dividere le righe in gruppi prima di aggregare, e HAVING a filtrare sul risultato aggregato. È il prossimo argomento.