Mais comment grouper des lignes d’une table MySQL selon une période de date ?

Mettons que j’ai une table MySQL avec une liste de commandes datées : id, date, amount

id	date	amount
1	2024-03-20	100
2	2024-09-03	50
3	2025-02-28	25
4	2025-05-02	25

Pour obtenir le montant total des commandes réalisé par année, je peux effectuer la requête suivante :

SELECT YEAR(date) as date_year, SUM(amount) as total
FROM mytable
GROUP BY YEAR(date)

Le YEAR(date) permet de récupérer uniquement la partie « année » de la date en question. J’aurai aussi pu utiliser EXTRACT(YEAR FROM date).

Cela va me donner :

date_year	total
2024	150
2025	50

Mais comment faire pour obtenir le total des commandes réalisé entre le 1 juillet et le 30 juin de l’année suivante ? Bref, comment grouper selon une période de date ?

J’ai l’impression que c’est aussi la question posée ici. La méthode GROUP BY ne permettant que de filtrer sur un nom de colonne, et non sur une condition, ce n’est pas simple. HAVING ne me sauvera pas, HAVING c’est « comme » un WHERE mais qui s’applique après le SELECT (i.e. il « connaît » les alias AS que l’on a créé) et après la résolution des functions d’agrégats (i.e. il sait faire une condition sur une somme, comme illustré ici).

Mais quand bien même GROUP BY fonctionnerait avec une condition, en y réfléchissant, je ne saurai pas tellement comment exprimer ce que je recherche comme une condition.

A vrai dire, je ne sais pas (encore) répondre à cette question. Mais en attendant, j’ai appris quelques petites choses sur SQL (i.e. MySQL) au passage et les voici.

Bon, j’ai envoyé cet article à ChatGPT et il a trouvé. Fichtre. Je n’aurai pas compris sa réponse si je n’avais pas cherché un peu au préalable. Mais quand même, c’est me fiche un coup à chaque fois. D’autant plus qu’il a compris que je voulais faire un groupement par année fiscale, ce dont je n’avais même pas parlé dans cet article ! (histoire de rire, je lui ai demandé de relire cette nouvelle version de l’article, il me félicite le petit malin, et il a même trouvé une coquille)

L’astuce ici, c’est que dans une condition CASE WHEN on peut générer une nouvelle valeur. On fait donc une condition :

• Si le mois est supérieur au mois de janvier, alors on créé un item « année en cours »-« année en cours + 1 »
• Sinon on créé « année en cours – 1 « -« année en cours »

Et il n’y a plus qu’à grouper sur ce résultat. Ce qui donne, avec un petit rollup pour avoir le somme total de toutes les années :

SELECT 
    CASE
      WHEN MONTH(date) >= 7 THEN CONCAT(YEAR(date), '-', YEAR(date) + 1)
      ELSE CONCAT(YEAR(date) - 1, '-', YEAR(date))
    END AS date_year_fiscal,
    SUM(amount) as total
FROM mytable
GROUP BY date_year_fiscal WITH ROLLUP

Ce qui donne bien :

date_year_fiscal	total
2023-2024	100
2024-2025	100
NULL	200

Et voici maintenant les astuces que j’avais trouvé entre temps. Je n’étais pas si loin.

CASE WHEN

Avec la fonction QUARTER(date) je peux récupérer le numéro du trimestre d’une date. J’ai découvert CASE WHEN qui permet d’exprimer une condition de type switch, et donc de faire un total par semestre :

SELECT
   YEAR(date) as date_year
   (CASE WHEN QUARTER(date) IN (1,2) THEN 1 ELSE 2 END) as date_semester,
   SUM(amount) as total
FROM mytable
GROUP BY YEAR(date), (CASE WHEN QUARTER(date) IN (1,2) THEN 1 ELSE 2 END)

J’obtiens alors le résultat suivant, que je peux re-traiter en dehors de SQL pour sommer les 2 morceaux de semestre qui m’intéresse. Ce n’est pas l’idéal, mais c’est déjà pas mal.

date_year	date_semester	total
2024	1	100
2024	2	50
2025	1	50

Avec CONCAT on peut aussi facilement rajouter une colonne qui indique le semestre de chaque année :
CONCAT(YEAR(date), '_', (CASE WHEN QUARTER(date) IN (1,2) THEN 1 ELSE 2 END)) as date_year_semester

Documentation et exemple d’utilisation :

• https://www.w3schools.com/SQL/func_mysql_case.asp

WITH ROLLUP

En rajoutant WITH ROLLUP à la clause GROUP BY, MySQL va s’occuper de calculer aussi la somme en groupant uniquement par la colonne YEAR(date), puis la somme totale de tout le data set.

SELECT
   YEAR(date) as date_year
   (CASE WHEN QUARTER(date) IN (1,2) THEN 1 ELSE 2 END) as date_semester,
   SUM(amount) as total
FROM mytable
GROUP BY YEAR(date), (CASE WHEN QUARTER(date) IN (1,2) THEN 1 ELSE 2 END) WITH ROLLUP

Ce qui donne le résultat suivant. Sympa. Ce n’est pas ce que je voulais, mais auparavant pour avoir ce genre de résultat, je faisais un retraitement en dehors de SQL :

date_year	date_semester	total
2024	1	100
2024	2	50
2024	NULL	150
2025	1	50
2025	NULL	50
NULL	NULL	200

Plus d’informations sur rollup ici et surtout cette documentation de MySQL qui explique comment renommer ces NULL disgracieux (et éviter de les confondre de vrais lignes contenant un NULL)

Autres pistes

• Une recherche sur une plage de dates avec BETWEEN.
  • https://www.w3schools.com/mysql/mysql_between.asp
  • https://stackoverflow.com/questions/6924896/having-without-group-by
• GROUP_CONCAT : https://dba.stackexchange.com/questions/196007/mysql-grouping-with-condition
• Le OVER qui est non-dispo en MySQL mais pourrait servir en d’autres circonstances
  • https://alaburda.github.io/posts/grouping-overlapping-date-ranges.html
  • https://thelinuxcode.com/sql-group-date/
• GROUPING : https://dev.mysql.com/blog-archive/mysql-8-0-grouping-function/
• Une astuce intéressante ici avec INTERVAL pour grouper par mois, mais sans commencer le 1er du mois. Mais c’est légèrement différent de ce que je cherche à faire.
• Une discussion intéressante ici, on ne doit pas être très loin