Docs SQLite exécutables : Fonctions d'agrégation

Q: Comment compter les valeurs distinctes en SQLite ?

Il suffit de glisser DISTINCT à l'intérieur de l'appel : COUNT(DISTINCT customer_id). Cela compte les valeurs uniques non nulles. À noter : COUNT(column) compte les valeurs non nulles, doublons inclus, tandis que COUNT(*) compte chaque ligne, peu importe les NULL.

Ce que fait réellement une fonction d'agrégation

La plupart des fonctions SQL que vous avez vues jusqu'ici travaillent ligne par ligne : UPPER(name) s'exécute une fois par ligne, ROUND(price, 2) aussi. Les fonctions d'agrégation SQLite, elles, fonctionnent autrement. Elles examinent un ensemble entier de lignes et le réduisent à une seule valeur.

Prépare une petite table pour expérimenter :

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;

Cinq lignes entrent, une seule en ressort. Voilà tout le modèle mental à retenir : les fonctions d'agrégation écrasent plusieurs lignes pour produire un résumé. Sans GROUP BY, ce résumé porte sur l'ensemble des lignes du résultat.

COUNT : compter des lignes ou compter des valeurs

COUNT se décline en trois variantes, et la nuance compte vraiment :

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 lignes_total,
    COUNT(amount)          AS lignes_avec_montant,
    COUNT(DISTINCT customer) AS clients_uniques
FROM orders;

COUNT(*) compte les lignes. NULL inclus. Renvoie toujours un nombre.
COUNT(column) compte les valeurs non NULL de la colonne.
COUNT(DISTINCT column) compte les valeurs uniques non NULL.

Cinq lignes, dont trois avec un amount, et trois clients distincts. Si un jour vous tombez sur un COUNT(amount) plus petit que COUNT(*) et que vous vous demandez pourquoi, c'est exactement ça : les NULL ne sont pas comptés.

SUM, AVG, MIN, MAX en SQLite

Les agrégats arithmétiques se comportent comme on s'y attend, avec une petite règle discrète : tous ignorent les 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 moyenne,
    MIN(amount) AS minimum,
    MAX(amount) AS maximum,
    COUNT(amount) AS nombre
FROM orders;

AVG donne (10 + 20 + 30) / 3 = 20.0, et non 60 / 4 = 15.0. Le dénominateur correspond au nombre de valeurs non NULL. Si ce n'est pas le comportement que vous cherchez — par exemple, si vous préférez considérer les données manquantes comme des zéros — il faut le dire explicitement :

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 et MAX fonctionnent aussi sur le texte et les dates : la comparaison se fait dans l'ordre lexicographique pour le texte, et sous forme de chaînes ISO pour le format de date standard.

SUM vs TOTAL en SQLite

SQLite propose une seconde fonction d'agrégation pour faire des sommes, TOTAL, qui corrige deux petits défauts agaçants de SUM :

CREATE TABLE empty_table (amount REAL);

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

SUM sur zéro ligne renvoie NULL. TOTAL renvoie 0.0.
SUM sur des valeurs toutes à NULL renvoie NULL. TOTAL renvoie 0.0.
TOTAL retourne toujours un nombre à virgule flottante, donc aucun risque de débordement d'entier.

Le compromis : TOTAL n'est pas standard, et ce résultat systématiquement en REAL peut surprendre si vous attendiez un entier. Privilégiez-le quand « aucune ligne signifie zéro » correspond à la logique de votre application, et restez sur SUM lorsque vous voulez le comportement standard SQL.

DISTINCT à l'intérieur des fonctions d'agrégation

DISTINCT peut s'utiliser dans n'importe quelle fonction d'agrégation, pas uniquement avec COUNT. Il élimine les doublons avant que l'agrégation ne s'exécute :

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) additionne le montant de chaque ligne. SUM(DISTINCT amount) additionne chaque montant unique une seule fois — pratique pour des cas du genre « total des montants de factures distincts », mais c'est rarement ce qu'on cherche. Le plus courant, c'est COUNT(DISTINCT customer).

La clause FILTER : agréger sur un sous-ensemble

Quand on veut agréger seulement certaines lignes, le réflexe naturel, c'est WHERE. Sauf que WHERE filtre tout d'un bloc — impossible, par exemple, de combiner « compter les commandes payées » et « compter les remboursements » dans une même requête de cette façon. C'est exactement ce que résout la clause FILTER :

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;

Chaque clause FILTER (WHERE ...) ne s'applique qu'à son propre agrégat. Un seul parcours de la table, plusieurs tranches résumées d'un coup. Avant l'arrivée de FILTER, on écrivait SUM(CASE WHEN status = 'paid' THEN amount END) — même résultat, mais bien plus verbeux.

GROUP_CONCAT : concaténer des chaînes en SQLite

GROUP_CONCAT fait figure d'exception. Au lieu de renvoyer un nombre, il assemble les valeurs en une seule chaîne :

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;

Le séparateur par défaut est la virgule. Pour en utiliser un autre, il suffit de passer un deuxième argument. L'ordre n'est pas garanti, sauf si vous écrivez l'appel sous la forme GROUP_CONCAT(tag ORDER BY tag) — pratique quand le résultat s'affiche dans une interface et que vous voulez qu'il reste stable.

Agrégation sans GROUP BY

Tous les exemples vus jusqu'ici qui utilisaient des fonctions d'agrégation sans GROUP BY renvoyaient exactement une seule ligne. C'est la règle : un SELECT avec des agrégats et sans GROUP BY produit une ligne unique qui résume toute la table (après application du WHERE).

Vous pouvez combiner plusieurs agrégats sans souci :

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 commandes,
    SUM(amount) AS chiffre_affaires,
    AVG(amount) AS moyenne,
    MIN(amount) AS plus_petit,
    MAX(amount) AS plus_grand
FROM orders
WHERE status = 'paid';

Ce que vous ne pouvez pas faire, c'est mélanger des colonnes non agrégées avec des fonctions d'agrégation en espérant obtenir un résultat cohérent :

-- Autorisé par SQLite, mais la valeur de `customer` est arbitraire.
SELECT customer, SUM(amount) FROM orders;

SQLite ne renverra pas d'erreur ici (d'autres SGBD, si), mais il va piocher au hasard le nom d'un client à afficher à côté du total. Si vous voulez un total par client, il te faut GROUP BY — c'est justement le sujet de la page suivante.

La suite : GROUP BY et HAVING

Les agrégats sur l'ensemble de la table répondent à la question « combien au total ». Les agrégats par groupe — par client, par mois, par statut — répondent à des questions bien plus intéressantes. La clause GROUP BY SQL sert à répartir les lignes dans des paquets avant d'agréger, et HAVING permet de filtrer sur le résultat agrégé. C'est ce qu'on voit ensuite.

Questions fréquentes

À quoi servent les fonctions d'agrégation en SQLite ?

Une fonction d'agrégation prend plusieurs lignes en entrée et renvoie une seule valeur de synthèse. SQLite en propose plusieurs en natif : COUNT, SUM, AVG, MIN, MAX, TOTAL et GROUP_CONCAT. Sans GROUP BY, elles réduisent l'ensemble du résultat à une seule ligne.

Quelle différence entre SUM et TOTAL en SQLite ?

Les deux additionnent des nombres, mais leur comportement diffère sur deux points. SUM renvoie NULL quand toutes les valeurs en entrée sont NULL et utilise l'arithmétique entière quand c'est possible (avec un risque de dépassement). TOTAL, lui, renvoie toujours un nombre à virgule flottante et retourne 0.0 s'il n'y a aucune ligne. En clair : prenez TOTAL si vous voulez la garantie d'un résultat numérique, et SUM si vous tenez au comportement standard SQL.

Comment compter les valeurs distinctes en SQLite ?

Il suffit de glisser DISTINCT à l'intérieur de l'appel : COUNT(DISTINCT customer_id). Cela compte les valeurs uniques non nulles. À noter : COUNT(column) compte les valeurs non nulles, doublons inclus, tandis que COUNT(*) compte chaque ligne, peu importe les NULL.

Les fonctions d'agrégation de SQLite ignorent-elles les NULL ?

Oui, à une exception près. Toutes les fonctions d'agrégation sautent les valeurs NULL, sauf COUNT(*). AVG, par exemple, divise par le nombre de valeurs non nulles, et non par le nombre total de lignes. COUNT(*), lui, compte les lignes et non les valeurs : les NULL sont donc bien pris en compte.

Concepts liés

SQLite : fonctions d'agrégation COUNT, SUM, AVG, MIN, MAX