03 - GROUP BY, HAVING et les fonctions d'aggregation

Ce que tu vas apprendre

• COUNT, SUM, AVG, MIN, MAX et les subtilites de NULL
• GROUP BY simple et multi-colonnes
• HAVING vs WHERE
• FILTER, STRING_AGG, ARRAY_AGG, ROLLUP et CUBE (PostgreSQL)

Prerequisites

Avoir lu les articles 01 et 02.

Les donnees

sqlCREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  product TEXT NOT NULL,
  category TEXT NOT NULL,
  amount NUMERIC(10,2) NOT NULL,
  quantity INT NOT NULL,
  status TEXT DEFAULT 'completed',
  sold_at DATE NOT NULL
);

INSERT INTO sales (product, category, amount, quantity, status, sold_at) VALUES
  ('Clavier', 'peripheriques', 89.99, 2, 'completed', '2025-01-15'),
  ('Souris', 'peripheriques', 59.99, 3, 'completed', '2025-01-20'),
  ('Ecran', 'moniteurs', 349.00, 1, 'completed', '2025-01-25'),
  ('Clavier', 'peripheriques', 89.99, 1, 'completed', '2025-02-10'),
  ('SSD', 'stockage', 79.99, 4, 'refunded', '2025-02-15'),
  ('Souris', 'peripheriques', 59.99, 2, 'completed', '2025-02-20'),
  ('Ecran', 'moniteurs', 349.00, 1, 'cancelled', '2025-03-01'),
  ('RAM', 'composants', 65.00, 5, 'completed', '2025-03-10'),
  ('Clavier', 'peripheriques', 89.99, 1, 'completed', '2025-03-15'),
  ('Casque', 'peripheriques', 129.99, 2, 'completed', '2025-03-20');

Les fonctions d'aggregation de base

sqlSELECT
  COUNT(*) AS nb_ventes,
  SUM(amount * quantity) AS chiffre_affaires,
  AVG(amount) AS prix_moyen,
  MIN(amount) AS prix_min,
  MAX(amount) AS prix_max
FROM sales;

 nb_ventes | chiffre_affaires | prix_moyen | prix_min | prix_max
-----------+------------------+------------+----------+----------
        10 |          2457.82 |    136.893 |    59.99 |   349.00

Rien de sorcier. Mais il y a un piège frequent avec COUNT.

COUNT(*) vs COUNT(column)

sql-- Ajoute une vente avec un product NULL (hypothetique)
-- Pour l'exemple, utilisons une autre colonne nullable
SELECT
  COUNT(*) AS total_lignes,
  COUNT(status) AS avec_status
FROM sales;

COUNT(*) compte toutes les lignes, y compris celles avec des NULL. COUNT(column) ne compte que les lignes ou cette colonne n'est pas NULL. La différence est subtile mais ca peut fausser tes rapports si tu n'y fais pas attention.

COUNT(DISTINCT column) est aussi utile :

sqlSELECT
  COUNT(*) AS nb_ventes,
  COUNT(DISTINCT product) AS nb_produits_differents
FROM sales;

 nb_ventes | nb_produits_differents
-----------+------------------------
        10 |                      5

GROUP BY

Regrouper les lignes par categorie et appliquer une aggregation :

sqlSELECT category, COUNT(*) AS nb_ventes, SUM(amount * quantity) AS total
FROM sales
GROUP BY category
ORDER BY total DESC;

   category     | nb_ventes | total
----------------+-----------+---------
 moniteurs      |         2 |  698.00
 peripheriques  |         5 |  889.90
 composants     |         1 |  325.00
 stockage       |         1 |  319.96

Regle d'or : toute colonne dans le SELECT qui n'est pas dans une fonction d'aggregation doit etre dans le GROUP BY. Sinon PostgreSQL te retourne une erreur (contrairement a MySQL qui fait un truc bizarre et non déterministe).

GROUP BY sur plusieurs colonnes

sqlSELECT
  category,
  date_trunc('month', sold_at)::DATE AS mois,
  COUNT(*) AS nb_ventes,
  SUM(amount * quantity) AS total
FROM sales
GROUP BY category, date_trunc('month', sold_at)
ORDER BY mois, category;

   category     |    mois    | nb_ventes | total
----------------+------------+-----------+--------
 moniteurs      | 2025-01-01 |         1 | 349.00
 peripheriques  | 2025-01-01 |         2 | 359.95
 peripheriques  | 2025-02-01 |         2 | 209.97
 stockage       | 2025-02-01 |         1 | 319.96
 composants     | 2025-03-01 |         1 | 325.00
 moniteurs      | 2025-03-01 |         1 | 349.00
 peripheriques  | 2025-03-01 |         2 | 349.97

date_trunc('month', sold_at) tronque la date au premier du mois. C'est la facon standard de faire des rapports mensuels en PostgreSQL.

HAVING : filtrer apres le GROUP BY

WHERE filtre les lignes AVANT le groupement. HAVING filtre les groupes APRES.

sql-- Categories avec plus de 2 ventes
SELECT category, COUNT(*) AS nb_ventes
FROM sales
GROUP BY category
HAVING COUNT(*) > 2;

   category     | nb_ventes
----------------+-----------
 peripheriques  |         5

L'ordre d'exécution SQL (simplifie) : FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. C'est pour ca que tu ne peux pas utiliser un alias de SELECT dans un WHERE (il n'existe pas encore a ce stade).

FILTER : l'arme secrete de PostgreSQL

C'est une extension PostgreSQL qui n'existe pas dans MySQL. Et c'est genial pour les rapports :

sqlSELECT
  category,
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'completed') AS completees,
  COUNT(*) FILTER (WHERE status = 'refunded') AS remboursees,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS annulees,
  SUM(amount * quantity) FILTER (WHERE status = 'completed') AS ca_reel
FROM sales
GROUP BY category
ORDER BY ca_reel DESC NULLS LAST;

   category     | total | completees | remboursees | annulees | ca_reel
----------------+-------+------------+-------------+----------+---------
 peripheriques  |     5 |          5 |           0 |        0 |  889.90
 moniteurs      |     2 |          1 |           0 |        1 |  349.00
 composants     |     1 |          1 |           0 |        0 |  325.00
 stockage       |     1 |          0 |           1 |        0 |  (null)

Sans FILTER, il faudrait écrire des CASE WHEN dans chaque aggregation. FILTER est nettement plus lisible. La doc PostgreSQL sur les fonctions d'aggregation en parle en détail.

STRING_AGG et ARRAY_AGG

Pour concatener des valeurs dans un groupe :

sql-- Liste des produits par categorie, separes par une virgule
SELECT category, STRING_AGG(DISTINCT product, ', ' ORDER BY product) AS produits
FROM sales
GROUP BY category;

   category     |       produits
----------------+----------------------
 composants     | RAM
 moniteurs      | Ecran
 peripheriques  | Casque, Clavier, Souris
 stockage       | SSD

ARRAY_AGG fait pareil mais retourne un tableau PostgreSQL au lieu d'une string :

sqlSELECT category, ARRAY_AGG(DISTINCT product ORDER BY product) AS produits
FROM sales
GROUP BY category;

   category     |       produits
----------------+-------------------------
 composants     | {RAM}
 moniteurs      | {Ecran}
 peripheriques  | {Casque,Clavier,Souris}
 stockage       | {SSD}

Quand tu construis une API, ARRAY_AGG combine avec json_agg permet de construire des réponses JSON directement en SQL sans faire de N+1 (on en reparle dans l'article 11).

GROUPING SETS, ROLLUP et CUBE

Pour les rapports multi-niveaux. Imagine que tu veux les totaux par categorie, par mois, ET le grand total dans une seule requête :

sqlSELECT
  category,
  date_trunc('month', sold_at)::DATE AS mois,
  SUM(amount * quantity) AS total
FROM sales
WHERE status = 'completed'
GROUP BY ROLLUP (category, date_trunc('month', sold_at))
ORDER BY category NULLS LAST, mois NULLS LAST;

   category     |    mois    |  total
----------------+------------+---------
 composants     | 2025-03-01 |  325.00
 composants     | (null)     |  325.00   <- sous-total composants
 moniteurs      | 2025-01-01 |  349.00
 moniteurs      | (null)     |  349.00   <- sous-total moniteurs
 peripheriques  | 2025-01-01 |  359.95
 peripheriques  | 2025-02-01 |  209.97
 peripheriques  | 2025-03-01 |  349.97
 peripheriques  | (null)     |  919.89   <- sous-total peripheriques
 (null)         | (null)     | 1593.89   <- grand total

ROLLUP généré les sous-totaux hierarchiques. CUBE généré toutes les combinaisons possibles (categorie seule, mois seul, les deux, le total). C'est l'équivalent de ce que fait Excel avec les tableaux croises dynamiques, mais en SQL pur. Utile pour les dashboards.

Sur paltemps.fr, on utilise ROLLUP pour générer les rapports mensuels des projets clients en une seule requête au lieu de faire 5 requêtes separees cote application.

Résumé

• COUNT(*) compte tout, COUNT(column) ignore les NULL
• WHERE filtre avant le GROUP BY, HAVING filtre apres
• FILTER (PostgreSQL) est plus lisible que CASE WHEN dans les aggregations
• STRING_AGG et ARRAY_AGG concatenent les valeurs d'un groupe
• ROLLUP et CUBE generent des sous-totaux dans une seule requête

Article précédent : 02 - Les JOINs Article suivant : 04 - Sous-requêtes et CTEs

Sources

• PostgreSQL Documentation - Aggregate Functions
• PostgreSQL Documentation - GROUPING SETS
• Modern SQL - FILTER clause