Regroupement de montant par année

**noureddine1967** · 01/12/2022, 09h44

Bonjour,

J'ai une table qui contient les créances de nos clients, il existe deux champs que je voudrais utiliser "date_facture" et "montant_facture".
En exécutant la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select substr(date_facturation,7,4) as annee, sum(montant_facture)
from tmp_creances_clients
group by annee
order by annee  desc

j'obtiens le résultat suivant :

annee montant_facture
2022 7654.82
2021 1518.95
2020 18218.39
2019 165744.34
2018 46991.41
2017 76701.13
2016 447148.53
2015 17713.54
2014 17713.54
2000 159766.95
1999 6558.68
1998 1031.14

Ce que je voudrais c'est d'avoir les quatre premières années 2022, 2021, 2020, 2019 à part et de regrouper le reste des montants des années 2018 et antérieur dans l'année 2018.

Merci par avance pour vos réponses

**tatayo** · 01/12/2022, 09h59

Bonjour,
Je ne sais pas si SqlLite accepte ce genre d'écriture, à tester:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
select (case when substr(date_facturation,7,4) < '2019' then '2018' else substr(date_facturation,7,4) end)  as annee, sum(montant_facture)
from tmp_creances_clients
group by annee
order by annee  desc

Il ne reste plus qu'à ajouter un calcul pour ne pas avoir à spécifier "en dur" l'année de bascule.

Tatayo.

**noureddine1967** · 01/12/2022, 10h05

Merci,

Ça marche sur Sqlite3, mais

Il ne reste plus qu'à ajouter un calcul pour ne pas avoir à spécifier "en dur" l'année de bascule.

Je vais essayer de le trouver.

**SergioMaster** · 01/12/2022, 10h58

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select strftime('%Y',current_date)-4

donc ceci devrait fonctionner

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select (case when strftime('%Y',date_facturation) < strftime('%Y',current_date)-3 then strftime('%Y',current_date)-4 else substr(date_facturation,7,4) end)  as annee, sum(montant_facture)
from tmp_creances_clients
group by annee
order by annee  desc

**noureddine1967** · 01/12/2022, 11h55

Merci infiniment.

**noureddine1967** · 16/01/2023, 11h14

Bonjour,

Dans le même contexte j'ai voulu utilisé la requête modifiée, pour regrouper les montants selon les 5 dernières années jusqu'à l'an 2018 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
SELECT (CASE WHEN max(annee) < max(annee) -4
                         THEN max(annee) -5 ELSE max(annee) END) AS annee, 
                         ROUND(SUM(montant_facture), 2) AS montant, 
                         ROUND(SUM(montant_facture)/t.total_general * 100, 2) AS taux 
                         FROM tmp_creances_clients,
                         (SELECT SUM(montant_facture) AS total_general FROM tmp_creances_clients a) AS t
                         GROUP BY annee
                         ORDER BY annee DESC

mais, le résultat affiche l'ensemble des années dans la table comme ceci :

Nom : liste.png
Affichages : 269
Taille : 6,5 Ko

Est ce que j'ai oublier quelque chose ???

Merci

**SergioMaster** · 16/01/2023, 11h37

Envoyé par noureddine1967

Est ce que j'ai oublié quelque chose ?

Oui, d'utiliser la colonne date_facturation

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
*
SELECT (CASE WHEN strftime('%Y',date_facturation) < strftime('%Y',date_facturation) -4
                         THEN strftime('%Y',date_facturation) -5 ELSE strftime('%Y',date_facturation) END) AS annee, 
                         ROUND(SUM(montant_facture), 2) AS montant, 
                         ROUND(SUM(montant_facture)/t.total_general * 100, 2) AS taux 
                         FROM tmp_creances_clients, (SELECT SUM(montant_facture) AS total_general FROM tmp_creances_clients a) AS t
                         GROUP BY annee
                         ORDER BY annee DESC

si SQlite supporte les CTE j'écrirai plutôt

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
WITH T AS (SELECT SUM(montant_facture) AS total_general FROM tmp_creances_clients a)
SELECT (CASE WHEN strftime('%Y',c.date_facturation) < strftime('%Y',c.date_facturation) -4
                         THEN strftime('%Y',c.date_facturation) -5 ELSE strftime('%Y',c.date_facturation) END) AS annee, 
                         ROUND(SUM(c.montant_facture), 2) AS montant, 
                         ROUND(SUM(c.montant_facture)/t.total_general * 100, 2) AS taux 
                         FROM tmp_creances_clients c JOIN  t ON 1=1
                         GROUP BY annee
                         ORDER BY annee DESC

**SergioMaster** · 16/01/2023, 11h38

Envoyé par noureddine1967

Merci infiniment.

il est d'usage de mettre un

sur les réponses acceptées

**noureddine1967** · 16/01/2023, 11h52

Merci SergioMaster,

Seulement, je voudrais utilisé le champ "annee" au lieu date_facturation et groupé les montants de l'année 2018 et antérieur.

**SergioMaster** · 16/01/2023, 16h40

Et alors ? Si je me base sur les premières réponses, je ne vois aucune colonne année dans la table tmp_creances_clients .

Quand vous posez des questions SQL il est bon de fournir la structure de la table pour commencer.

**noureddine1967** · 17/01/2023, 08h44

Merci SergioMaster pour votre patience,

En effet, au début j'ai ecrit ma requête en fonction de la date de facturation.
Mais, en raison de la date de facturation du dernier mois de l'année qui s'effectue au premier mois de l'année qui suit. Là, j'ai constaté que mes calculs sont, en quelques sorte, faussé.

A cet effet, j'ai décidé d'extraire "l'année" du champ "num_facture" qui lui correspond exactement à l'année.

Par conséquent, ma view "tmp_creances_clients" contient les champs suivants : code_client, num_facture, date_facturation, montant et annee

Dans le même contexte j'ai voulu utilisé la requête modifiée, pour regrouper les montants selon les 5 dernières années jusqu'à l'an 2018 :

Ainsi, à partir de la dernière année, "MAX(annee)" qui est 2022, je calcul mes soldes : 2021, 2020, 2019 et je cumul le reste avec l'année 2018.
J'espère qui j'ai bien pu expliquer mon problème.

**SergioMaster** · 17/01/2023, 08h57

Envoyé par noureddine1967

pour votre patience

Je ne vous le fais pas dire !

Mais, en raison de la date de facturation du dernier mois de l'année qui s'effectue au premier mois de l'année qui suit.

Clair comme de la vase ! Mais je ne vais pas épiloguer, tout comme je ne vais pas écrire qu'un script de création de table ainsi qu'un script de remplissage de données test permettrait de faire une réponse contrôlée

voici ma proposition

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
WITH T AS (SELECT SUM(montant_facture AS total_general, max(annee) AN FROM tmp_creances_clients)
SELECT (CASE WHEN T.AN< T.AN -4 THEN TAN-5 ELSE TAN) AS annee, 
                         ROUND(SUM(c.montant_facture), 2) AS montant, 
                         ROUND(SUM(c.montant_facture)/t.total_general * 100, 2) AS taux 
                         FROM tmp_creances_clients c JOIN  t ON 1=1
                         GROUP BY annee
                         ORDER BY annee DESC

**noureddine1967** · 17/01/2023, 09h33

Merci encore,

J'ai exécuter votre requête:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
WITH T AS (SELECT SUM(montant_facture) AS total_general, MAX(annee) AS AN FROM tmp_creances_clients a)
SELECT (CASE WHEN T.AN<T.AN-4 THEN T.AN-5 ELSE T.AN END) AS annee, 
ROUND(SUM(c.montant_facture), 2) AS montant, 
ROUND(SUM(c.montant_facture)/T.total_general * 100, 2) AS taux 
FROM tmp_creances_clients c JOIN T ON 1=1
GROUP BY annee
ORDER BY annee DESC

Le résultat affiche uniquement l'année 2022:
Nom : Sans titre.png
Affichages : 248
Taille : 4,6 Ko

**SergioMaster** · 17/01/2023, 10h08

Ah, j'aurais pu le découvrir si j'avais eu un jeu d'essai.

Comme j'ai écrit le SQL à la volée,

j'ai fait des confusions

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
WITH T AS (SELECT SUM(montant_facture) total_general, MAX(annee) AN FROM tmp_creances_clients a)
SELECT (CASE WHEN c.annee>T.AN-4 THEN c.Annee else  T.an-4 END)  annee, 
ROUND(SUM(c.montant_facture), 2) montant, 
ROUND(SUM(c.montant_facture)/T.total_general * 100, 2)  taux 
FROM tmp_creances_clients c JOIN T ON 1=1
GROUP BY annee
ORDER BY annee DESC

**noureddine1967** · 17/01/2023, 10h19

Pour éliminer toute confusion j'ai remplacé annee_r par exercice

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
 
WITH T AS (SELECT SUM(montant_facture) AS total_general, MAX(annee) AS AN FROM tmp_creances_clients a)
SELECT (CASE WHEN T.AN < T.AN-4 THEN T.AN-5 ELSE T.AN END) AS exercice, 
ROUND(SUM(c.montant_facture), 2) AS montant, 
ROUND(SUM(c.montant_facture)/T.total_general * 100, 2) AS taux 
FROM tmp_creances_clients c JOIN T ON 1=1
GROUP BY exercice
ORDER BY exercice DESC

Cette fois le résultat une seule ligne :
Nom : Sans titre.png
Affichages : 246
Taille : 1,3 Ko

**SergioMaster** · 17/01/2023, 10h45

mon dernier post est corrigé, et grâce à un jeu d'essai bidon
Nom : Capture.PNG
Affichages : 247
Taille : 17,3 Ko

on ne m'otera pas de la tête qu'avec des fonctions de fenêtrage https://www.sqlite.org/windowfunctions.html il serait possible de faire quelque chose, mais je maitrise mal

**noureddine1967** · 17/01/2023, 11h13

Merci SergioMaster,

Mais, j'ai dû remplacer annee par exercice

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
WITH T AS (SELECT SUM(montant_facture) AS total_general, MAX(annee) AS AN FROM tmp_creances_clients a)
SELECT (CASE WHEN c.annee >T.AN-4 THEN c.annee ELSE T.AN-4 END) AS exercice, 
ROUND(SUM(c.montant_facture), 2) AS montant_an, 
ROUND(SUM(c.montant_facture)/T.total_general * 100, 2) AS taux
FROM tmp_creances_clients c JOIN T ON 1=1
GROUP BY exercice
ORDER BY exercice DESC

Nom : Sans titre.png
Affichages : 243
Taille : 3,5 Ko

**Waldar** · 17/01/2023, 11h38

Envoyé par SergioMaster

on ne m'otera pas de la tête qu'avec des fonctions de fenêtrage https://www.sqlite.org/windowfunctions.html il serait possible de faire quelque chose, mais je maitrise mal

Comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
with cte_exercice (exercice, montant_facture) as
(
select max(annee, max(annee) over() -4)
     , montant_facture
  from tmp_creances_clients
)
  select exercice
       , round(sum(montant_facture), 2) as montant_an
       , round(100 * sum(montant_facture) / sum(sum(montant_facture)) over(), 2) as taux
    from cte_exercice
group by exercice
order by exercice desc;