IIF et GROUP BY (sous Access)

Question

Bonjour, 

Débutant (depuis 5 ans) en SQL sous ACCESS, je n'arrive pas à finaliser un groupement sur une ligne avec la requete suivante:

SELECT YEAR(DateDocument)
       ,MONTH(DateDocument)
              ,IIF(Article.Famille like '0%',SUM(`PrixTotalLigne`),0) AS Gamme0
              ,IIF(Article.Famille like '1%',SUM(`PrixTotalLigne`),0) AS Gamme1
              ,IIF(Article.Famille like '2%',SUM(`PrixTotalLigne`),0) AS Gamme2
      FROM `LigneCdeClient`,Article
 WHERE Article.Code=LigneCdeClient.CodeArticle
 GROUP BY YEAR(DateDocument)
  ,MONTH(DateDocument)
         ,Article.Famille like '0%'
         ,Article.Famille like '1%'
                ,Article.Famille like '2%'

La requete me rend 3 lignes par groupement mois/année alors que j'en recherche une seule (pour injection ultérieure dans Excel).
Evidemment si je retire les trois dernières lignes, j'ai une erreur
et si je groupe par Article.Famille (sans le like), j'ai autant de lignes que de familles.

Une idée ?

Merci d'avance à tous.

jacofee · Answer

Bonjour,

Je viens de tomber sur votre question posée il y a un mois. J'espère que ma réponse vous sera utile, sinon qu'elle pourra aider quelque développeur faisant face au même problème.

La difficulté que vous rencontrez résulte  d'une mauvaise compréhension du fonctionnement du group by.
Lorsqu'il rencontre la clause GROUP BY l'optimiseur de la base (la partie du serveur qui analyse les requêtes et met en oeuvre la meilleure politique d'accès aux données) examine la liste des colonnes figurant dans la clause SELECT et la compare avec celle figurant dans la clause GROUP BY. Chaque colonne figurant dans le select doit soit faire l'objet d'une opération d'agrégation (un sum par exemple), soit figurer dans le GROUP BY. L'optimiseur filtre réalise alors la jointure, applique les filtres de la clause where et découpe le résultat en groupes selon ce qui figure dans la clause GROUP BY. Notez en passant que les opérations sont réalisées dans cet ordre, ce qui a des implications en termes de performance et de logique.

Votre requête ne peut que retourner une table contenant une ligne par famille d'articles puisque ces dernières figurent dans la clause SELECT. Le fait que vous ne les affichiez pas explicitement n'a aucune importance car elles sont présentes dans les expressions définissant les colonnes Gamme0, Gamme1 et Gamme2:
 ,IIF(Article.Famille like '0%',SUM(`PrixTotalLigne`),0) AS Gamme0
 ,IIF(Article.Famille like '1%',SUM(`PrixTotalLigne`),0) AS Gamme1
 ,IIF(Article.Famille like '2%',SUM(`PrixTotalLigne`),0) AS Gamme2

En conséquence le moteur de la base de données n'a pas d'autre choix que de créer un regroupement pour chaque valeur de la sous-chaîne de famille figurant dans les formules IIF:
 0%
 1%
 2%
Pour cette même raison, si vous retirez les trois dernières lignes, le moteur de la base génère une erreur parce qu'il ne sait pas dans quel ordre il doit traiter ces regroupements.

L'opération que vous cherchez à réaliser s'appelle un pivot, c'est à dire créer une table dont une partie des colonnes est définie par les valeurs des lignes d'une colonne de la jointure source. Les SGBD savent très bien réaliser ce genre de transformation. Avec Access vous avez deux solutions pour obtenir le résultat que vous recherchez :
 1) Créer une requête réalisant le pivot, mais sans agrégation. Pour cela enlevez le group by et les SUM :
   SELECT YEAR(DateDocument) as annee
      ,MONTH(DateDocument) as Mois
       ,IIF(Article.Famille like '0%',`PrixTotalLigne`,0) AS Gamme0
       ,IIF(Article.Famille like '1%',`PrixTotalLigne`,0) AS Gamme1
       ,IIF(Article.Famille like '2%',`PrixTotalLigne`,0) AS Gamme2
     FROM `LigneCdeClient`,Article
   WHERE Article.Code=LigneCdeClient.CodeArticle
  Vous reprenez alors le résultat de cette requête en réalisant l'agrégation en regroupant les données par année et mois :
   SELECT annee, mois, sum(Gamme0) as Gamme0, SUM(Gamme1) as Gamme1, SUM(Gamme2) as Gamme2
   FROM (
    SELECT YEAR(DateDocument) as annee
       ,MONTH(DateDocument) as Mois
        ,IIF(Article.Famille like '0%',`PrixTotalLigne`,0) AS Gamme0
        ,IIF(Article.Famille like '1%',`PrixTotalLigne`,0) AS Gamme1
        ,IIF(Article.Famille like '2%',`PrixTotalLigne`,0) AS Gamme2
      FROM `LigneCdeClient`,Article
    WHERE Article.Code=LigneCdeClient.CodeArticle
   )
   GROUP BY annee, mois
  Vous noterez que la première requête est une sous-requête, ce que l'on appelle également un table dérivée.
 2) Vous réalisez les deux opérations en une seule passe grâce à la clause TRANSFORM PIVOT sous ACCESS :
   TRANSFORM Sum(Article.PrixTotalLigne) AS SommeDePrixTotalLigne
   SELECT Year([DateDocument]) AS Année, Month([DateDocument]) AS Mois
   FROM  `LigneCdeClient`,Article
    WHERE Article.Code=LigneCdeClient.CodeArticle
   and (Article.Famille ALike "0%" OR Article.Famille ALike "1%" OR Article.Famille ALike "2%")
   GROUP BY Year([DateDocument]), Month([DateDocument])
   PIVOT Left([Article].[Famille],2);
  Le mot-clé TRANSFORM est propre à ACCESS. Cette solution fonctionnera parce que vous ne cherchez à agréger qu'une seule colonne qui est PrixTotalLigne.
  Notez également que le nombre et le nom des colonnes obtenues après le pivot dépend des valeurs contenues dans le résultat de la jointure. Si ce dernier ne contient pas une des valeurs attendues (par exemple il n'y a aucune famille commençant par 0) alors la colonne correspondante ne sera pas créée.

Si votre besoin est d'agréger plusieurs colonnes (ex: prix et quantité) ou si vous voulez obtenir un nombre de colonnes fixes quelque soit le contenu du résultat de la jointure, alors la première solution s'impose. 
 
Cordialement,

IIF et GROUP BY (sous Access)

1 réponse

Votre réponse

Discussions similaires