Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Membre du Club
    Inscrit en
    mai 2009
    Messages
    187
    Détails du profil
    Informations forums :
    Inscription : mai 2009
    Messages : 187
    Points : 55
    Points
    55

    Par défaut Requète pour faire des cumuls mensuels de comptabilité.

    A partir de la table des écritures journalières, je désire présenter les cumuls mensuels, année par année, regroupés par compte et sous-compte. Je dois avouer que cela me semble au delà de ce que je sais faire en MySQL, mais je tente : voici ce que j'ai essayé :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
     
    $req="SELECT Y, CP, SC, G, Comptes.Compte, Sous_Comptes.Sous_compte,
                           SUM(D-C) WHERE M = 1 as JAN,
                           SUM(D-C) WHERE M = 2 as FEV,
                           SUM(D-C) WHERE M = 3 as MAR,
                           SUM(D-C) WHERE M = 4 as AVR,
                           SUM(D-C) WHERE M = 5 as MAI,
                           SUM(D-C) WHERE M = 6 as JuN,
                           SUM(D-C) WHERE M = 7 as JUL,
                           SUM(D-C) WHERE M = 8 as AOU,
                           SUM(D-C) WHERE M = 9 as SEP,
                           SUM(D-C) WHERE M = 10 as OCT,
                           SUM(D-C) WHERE M = 11 as NOV,
                           SUM(D-C) WHERE M = 12 as DEC
                FROM (SELECT YEAR(DateOP) as Y, MONTH(DateOP) as M,
                                       E.Compte as CP, E.Sous_Compte as SC,
                                       Debit as D, Credit as C
    			FROM Ecritures as E)
                GROUP BY Y, CP, SC
                INNER JOIN Comptes ON Comptes.Compte=CP
                INNER JOIN Sous_Comptes on Sous_Comptes.Compte = CP and
                                  Sous_Comptes.Numero = SC
    	   ORDER BY Y, Compte, Sous_compte";
    MySQL me dit (très gentiment) que j'ai une erreur "près de 'WHERE M = 1 as JAN,...'"

    Je souhaiterais, non pas que l'on me donne la requête toute faite (si c'est possible), mais comprendre, et mon erreur, et ce qu'il aurait fallu faire.

    Merci d'avance;

    AMIcalement.

  2. #2
    Expert éminent
    Homme Profil pro
    Responsable Datas
    Inscrit en
    janvier 2009
    Messages
    3 910
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Responsable Datas

    Informations forums :
    Inscription : janvier 2009
    Messages : 3 910
    Points : 9 073
    Points
    9 073

    Par défaut

    Bonjour,
    Elle pique un peu les yeux cette requête…
    Pour le SUM, la bonne syntaxe serait de ce genre:
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    ...
    sum(case when M = 1 then D - c else 0 end)
    Le GROUP BY doit se retrouver après les jointures, et non avant, et doit spécifier toutes les colonnes qui ne sont pas agrégées.
    Je ne vois pas trop à quoi sert cette sous-requête d'ailleurs, mais si tu veux la garder il faudrait revoir les jointures (INNER JOIN est bien plus clair).

    Voilà, quelques pistes pour avancer.

    Tatayo.

  3. #3
    Membre du Club
    Inscrit en
    mai 2009
    Messages
    187
    Détails du profil
    Informations forums :
    Inscription : mai 2009
    Messages : 187
    Points : 55
    Points
    55

    Par défaut

    Merci de la réponse. Voici le nouvel essai :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    $req="SELECT Y, CP, SC, Comptes.Genre, Comptes.Compte, Sous_Comptes.Sous_compte,
                                      SUM(CASE M = 1 THEN D - C ELSE 0 END) AS JAN,
                                      SUM(CASE M = 2 THEN D - C ELSE 0 END) AS FEV,
                                      SUM(CASE M = 3 THEN D - C ELSE 0 END) AS MAR,
                                      SUM(CASE M = 4 THEN D - C ELSE 0 END) AS AVR,
                                      SUM(CASE M = 5 THEN D - C ELSE 0 END) AS MAI,
                                      SUM(CASE M = 6 THEN D - C ELSE 0 END) AS JUN,
                                      SUM(CASE M = 7 THEN D - C ELSE 0 END) AS JUL,
                                      SUM(CASE M = 8 THEN D - C ELSE 0 END) AS AOU,
                                      SUM(CASE M = 9 THEN D - C ELSE 0 END) AS SEP,
                                      SUM(CASE M = 10 THEN D - C ELSE 0 END) AS OCT,
                                      SUM(CASE M = 11 THEN D - C ELSE 0 END) AS NOV,
                                      SUM(CASE M = 12 THEN D - C ELSE 0 END) AS DEC
                           FROM (SELECT YEAR(DateOP) as Y, MONTH(DateOP) as M,
                                                   E.Compte as CP, E.Sous_Compte as SC,
                                                   Debit as D, Credit as C
                                       FROM Ecritures as E)
                           INNER JOIN Comptes ON Comptes.Compte=CP
                           INNER JOIN Sous_Comptes on Sous_Comptes.Compte = CP AND
                                             Sous_Comptes.Numero = SC
                           GROUP BY Y, CP, SC
                           ORDER BY Y, Compte, Sous_compte";
    Avec un résultat à peu près semblable :
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'THEN D - C ELSE 0 END) AS JAN,
    Est-ce que ce message ne suggère pas que l'erreur, ici, vient de CASE qui serait mal utilisé ? J'ai visité un site qui évoque COALESCE pour un cas à peu près semblable. Dans ma table originale, en principe, il y a soit Credit, soit Debit qui portent une valeur, l'autre étant à NULL.

    "Le GROUP BY doit spécifier toutes les colonnes qui ne sont pas agrégées" : je ne comprends pas cette phrase. Ce que je voulais dire, c'est qu'il faut faire les sommes pour toutes les ligne où les valeurs de Y, CP et SC sont égales. Est-ce que cette explication est claire ?

    La sous-requête n'est sans doute pas indispensable, mais elle sert à créer les champs Y et M, qui n'existent pas dans la table originale. Il me semble que dans ce cas, je dois mettre tous les champs que je sélectionne dans la requête principale. Non ?

    Les INNER JOIN servent à transformer CP et SC (qui sont des integer) en Compte et Sous-compte (string) bien plus lisible pour les humains. Veux-tu dire qu'il serait plus clair de les placer dans la sous-requête ? Il ne me semble pas.

  4. #4
    Expert confirmé
    Profil pro
    Inscrit en
    août 2008
    Messages
    2 802
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : août 2008
    Messages : 2 802
    Points : 5 496
    Points
    5 496

    Par défaut

    Citation Envoyé par L'AMI Yves Voir le message
    Est-ce que ce message ne suggère pas que l'erreur, ici, vient de CASE qui serait mal utilisé ?
    Il manque le WHEN

    Citation Envoyé par L'AMI Yves Voir le message
    "Le GROUP BY doit spécifier toutes les colonnes qui ne sont pas agrégées" : je ne comprends pas cette phrase.
    La remarque sur le GROUP BY correspond à ce problème :
    https://cedric-duprez.developpez.com...r-group-by/#L4

    Citation Envoyé par L'AMI Yves Voir le message
    La sous-requête n'est sans doute pas indispensable, mais elle sert à créer les champs Y et M, qui n'existent pas dans la table originale.
    Pas besoin de sous-requête, mais ça n'est pas non plus un problème d'en faire une.

    Ça pourrait donner quelque chose comme ça :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    SELECT YEAR(e.DateOP), Comptes.Genre, Comptes.Compte, Sous_Comptes.Sous_compte
         , SUM(case when MONTH(e.DateOP) = 1  THEN COALESCE(e.Debit,0) - COALESCE(e.Credit,0) ELSE 0 END) AS JAN
         , SUM(case when MONTH(e.DateOP) = 2  THEN COALESCE(e.Debit,0) - COALESCE(e.Credit,0) ELSE 0 END) AS FEV
         , SUM(case when MONTH(e.DateOP) = 3  THEN COALESCE(e.Debit,0) - COALESCE(e.Credit,0) ELSE 0 END) AS MAR
         , SUM(case when MONTH(e.DateOP) = 4  THEN COALESCE(e.Debit,0) - COALESCE(e.Credit,0) ELSE 0 END) AS AVR
         , SUM(case when MONTH(e.DateOP) = 5  THEN COALESCE(e.Debit,0) - COALESCE(e.Credit,0) ELSE 0 END) AS MAI
         , SUM(case when MONTH(e.DateOP) = 6  THEN COALESCE(e.Debit,0) - COALESCE(e.Credit,0) ELSE 0 END) AS JUN
         , SUM(case when MONTH(e.DateOP) = 7  THEN COALESCE(e.Debit,0) - COALESCE(e.Credit,0) ELSE 0 END) AS JUL
         , SUM(case when MONTH(e.DateOP) = 8  THEN COALESCE(e.Debit,0) - COALESCE(e.Credit,0) ELSE 0 END) AS AOU
         , SUM(case when MONTH(e.DateOP) = 9  THEN COALESCE(e.Debit,0) - COALESCE(e.Credit,0) ELSE 0 END) AS SEP
         , SUM(case when MONTH(e.DateOP) = 10 THEN COALESCE(e.Debit,0) - COALESCE(e.Credit,0) ELSE 0 END) AS OCT
         , SUM(case when MONTH(e.DateOP) = 11 THEN COALESCE(e.Debit,0) - COALESCE(e.Credit,0) ELSE 0 END) AS NOV
         , SUM(case when MONTH(e.DateOP) = 12 THEN COALESCE(e.Debit,0) - COALESCE(e.Credit,0) ELSE 0 END) AS DEC
      FROM Ecritures e 
     INNER JOIN Comptes ON Comptes.Compte = E.Compte
     INNER JOIN Sous_Comptes on Sous_Comptes.Compte = E.Compte 
                            and Sous_Comptes.Numero = E.Sous_Compte
     GROUP BY YEAR(e.DateOP), Comptes.Genre, Comptes.Compte, Sous_Comptes.Sous_compte
     ORDER BY (e.DateOP), Comptes.Compte, Sous_Comptes.Sous_compte

  5. #5
    Membre du Club
    Inscrit en
    mai 2009
    Messages
    187
    Détails du profil
    Informations forums :
    Inscription : mai 2009
    Messages : 187
    Points : 55
    Points
    55

    Par défaut

    Merci encore pour cette nouvelle réponse.

    J'ai préféré garder la sous-requête, mais il a fallu que je lui donne un alias (nécessaire, même s'il n'est pas utilisé). J'ai ensuite eu une erreur sur "DEC" (?) que j'ai corrigé en "DE", puis il a eu une ambiguïté sur "Compte", et il a fallu que j’ajoute "Genre" dans la clause "Group By", bien que dans la logique de mes données, cela fasse double emploi avec "Compte" (mais je veux bien admettre que MySQL ne le sache pas).

    Voici la version actuelle :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
     
    $req="SELECT Y, CP, SC, Comptes.Genre, Comptes.Compte AS Cpt, Sous_Comptes.Sous_compte AS Scp,
                           SUM(CASE WHEN M = 1  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS JAN,
                           SUM(CASE WHEN M = 2  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS FEV,
                           SUM(CASE WHEN M = 3  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS MAR,
                           SUM(CASE WHEN M = 4  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS AVR,
                           SUM(CASE WHEN M = 5  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS MAI,
                           SUM(CASE WHEN M = 6  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS JUN,
                           SUM(CASE WHEN M = 7  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS JUL,
                           SUM(CASE WHEN M = 8  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS AOU,
                           SUM(CASE WHEN M = 9  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS SEP,
                           SUM(CASE WHEN M = 10  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS OCT,
                           SUM(CASE WHEN M = 11  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS NOV,
                           SUM(CASE WHEN M = 12  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS DE
               FROM (SELECT YEAR(DateOP) AS Y, MONTH(DateOP) AS M,
                                      E.Compte AS CP, E.Sous_Compte AS SC,
                                      Debit AS D, Credit AS C
                           FROM Ecritures AS E) AS EE
               INNER JOIN Comptes ON Comptes.Compte=CP
               INNER JOIN Sous_Comptes ON Sous_Comptes.Compte = CP AND
                                 Sous_Comptes.Numero = SC
               GROUP BY Y, CP, SC, Genre
               ORDER BY Y, Cpt, Scp";
    Cette requête donne un résultat, et sans erreur. Mais le "GROUP BY" ne donne pas le résultat attendu : dans mes données, il y a environ combinaisons différentes de "Compte, Sous-compte et Genre", et deux années. Or la réponse contient 6 lignes, trois par année, avec les mêmes combinaisons de Compte et Sous-compte.

    Pour moi, c'est assez incompréhensible.

    Merci de m'aider encore à comprendre...

    AMIcalement.

  6. #6
    Expert éminent
    Homme Profil pro
    Responsable Datas
    Inscrit en
    janvier 2009
    Messages
    3 910
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Responsable Datas

    Informations forums :
    Inscription : janvier 2009
    Messages : 3 910
    Points : 9 073
    Points
    9 073

    Par défaut

    Pour moi le problème vient de là:
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
     
    SELECT Y, CP, SC, Comptes.Genre, Comptes.Compte AS Cpt, Sous_Comptes.Sous_compte AS Scp,
                           SUM(CASE WHEN M = 1  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS JAN,
    ...
    GROUP BY Y, CP, SC, Genre
    Dans le GROUP BY il manque des colonnes qui sont dans le SELECT (cpt, Scp). Dans ce cas la plupart des SGBDs ne laissent pas passer la requête, et MySQL fait "ce qu'il peut", en gros prendre la première valeur qu'il trouve.

    Tatayo.

  7. #7
    Membre du Club
    Inscrit en
    mai 2009
    Messages
    187
    Détails du profil
    Informations forums :
    Inscription : mai 2009
    Messages : 187
    Points : 55
    Points
    55

    Par défaut

    Merci Tatayo.

    Je ne cherche pas à te contredire, mais, du fait de l'"INNER JOIN", il y a équivalence entre CP et Compte d'une part, et entre SC et Sous-compte d'autre part. Ta réponse veut-elle dire que MySQL n'est pas assez "intelligent" pour le savoir ?

    De toutes façons, je vais faire un essai, et je verrai bien.

    Je viens donner le résultat dès que c'est fait.

    AMIcalement.

  8. #8
    Membre du Club
    Inscrit en
    mai 2009
    Messages
    187
    Détails du profil
    Informations forums :
    Inscription : mai 2009
    Messages : 187
    Points : 55
    Points
    55

    Par défaut

    Alors je dois faire amende honorable : c'est de ma faute : voici la requête qui me donne satisfaction :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    	$req="SELECT Y, CP, SC, Comptes.Genre, Comptes.Compte AS Cpt, Sous_Comptes.Sous_compte AS Scp,
                                           SUM(CASE WHEN M = 1  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS JAN,
                                           SUM(CASE WHEN M = 2  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS FEV,
                                           SUM(CASE WHEN M = 3  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS MAR,
                                           SUM(CASE WHEN M = 4  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS AVR,
                                           SUM(CASE WHEN M = 5  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS MAI,
                                           SUM(CASE WHEN M = 6  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS JUN,
                                           SUM(CASE WHEN M = 7  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS JUL,
                                           SUM(CASE WHEN M = 8  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS AOU,
                                           SUM(CASE WHEN M = 9  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS SEP,
                                           SUM(CASE WHEN M = 10  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS OCT,
                                           SUM(CASE WHEN M = 11  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS NOV,
                                           SUM(CASE WHEN M = 12  THEN COALESCE(D,0) - COALESCE(C,0) ELSE 0 END) AS DE
                               FROM (SELECT YEAR(DateOP) AS Y, MONTH(DateOP) AS M,
                                                      E.Compte AS CP, E.Sous_Compte AS SC,
                                                      Debit AS D, Credit AS C
                                           FROM Ecritures AS E) AS EE
                               INNER JOIN Comptes ON Comptes.Numero=CP
                               INNER JOIN Sous_Comptes ON Sous_Comptes.Compte = CP AND
                                                 Sous_Comptes.Numero = SC
                               GROUP BY Y, CP, SC
                               ORDER BY Y, Cpt, Scp";
    Regardez l'erreur de l"INNER JOIN" que j'ai mise en vert. Évidemment, ça change tout. Peut-être que MySQL a "besoin" que tout ce qui est en SELECT soit aussi en INNER JOIN, mais, si ça n'y est pas, il fait ce qu'il peut, et, quand certains éléménts du SELECT ne servent à rien dans l'INNER JOIN, c'est ce qu'il faut.

    Mon problème est résolu, grâce à votre aide : MERCI.

    AMIcalement.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Programme pour faire des requêtes mensuelles
    Par chisac dans le forum Général Java
    Réponses: 9
    Dernier message: 03/01/2013, 12h20
  2. Requêtes pour faire des statistiques
    Par fou-jea dans le forum Langage SQL
    Réponses: 4
    Dernier message: 06/09/2012, 15h40
  3. Réponses: 2
    Dernier message: 02/03/2010, 12h50
  4. Outil graphique pour faire des requêtes ?
    Par falco- dans le forum SGBD
    Réponses: 0
    Dernier message: 27/01/2010, 12h48
  5. Réponses: 5
    Dernier message: 23/02/2007, 09h04

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo